package flight;

import common.*;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.Vector;

/**
 * 
 * @author Tracy
 */
public class FlightPageBean extends PageBusiness {

    private Connection con;
    private FlightBean fb;      // search conditions
    private int tripType;       // 1 means round-trip, 2 means single-way
    private int currentType;    // current display type: 1 means departure trip, 2 means return trip
	
    public FlightPageBean() throws Exception {
        super();
        con = DbConnector.getConnection();
        v = new Vector();
        v2 = new Vector();
    }
	
    public int getAvailableCount() throws Exception {
        // TEMP: FOR TEST, SHOULD BE "FLIGHT"
        return this.getAvailableCountHelper(con,"AIRLINE");
    }

    @Override
    public PageBean listData(String page) throws Exception {
        try
        {
            String sqlDepart1hop = "select ap.AIRPORT_CODE as DEPART_AIRPORT, a.AIRPORT_CODE as ARRIVE_AIRPORT, fl.FLIGHT_PRICE as PriceTotal, fl.FLIGHT_PRICE as FIRST_FLIGHT_PRICE, fl.FLIGHT_ID as FIRST_FLIGHT_ID, ap.AIRPORT_CITY as DEPART_AIRPORT_CITY, ap.AIRPORT_STATE as DEPART_AIRPORT_STATE, " +
                    "a.AIRPORT_CITY as ARRIVE_AIRPORT_CITY, a.AIRPORT_STATE as ARRIVE_AIRPORT_STATE, " +
                    "al.AIRLINE_NAME, al.AIRLINE_ABBREVIATION, fl.FLIGHT_NUMBER, to_char(fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FLIGHT_DEPARTURE_TIME, " +
                    "to_char(fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FLIGHT_ARRIVAL_TIME from " +
                    "AIRPORT ap, AIRPORT a, AIRLINE al, FLIGHT fl where " +
                    "ap.AIRPORT_CITY='" + fb.getDepartCity() + "' and ap.AIRPORT_ID=fl.DEPART_FROM and " +
                    "al.AIRLINE_ID=fl.AIRLINE_ID and a.AIRPORT_CITY='" + fb.getArriveCity() + "' and " +
                    "a.AIRPORT_ID=fl.ARRIVE_AT and EXTRACT(YEAR FROM " +
                    "fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartYear() + " and EXTRACT(MONTH FROM " +
                    "fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartMonth() + " and EXTRACT(DAY FROM " +
                    "fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartDay() + " and EXTRACT(HOUR FROM " +
                    "fl.FLIGHT_DEPARTURE_TIME)<=" + fb.getDepartHourUpper() + " and EXTRACT(HOUR FROM " +
                    "fl.FLIGHT_DEPARTURE_TIME)>=" + fb.getDepartHourLower()+
                    " and EXTRACT(HOUR FROM fl.FLIGHT_ARRIVAL_TIME)<=" + fb.getDepartArrivalHourUpper() +
                    " and EXTRACT(HOUR FROM fl.FLIGHT_ARRIVAL_TIME)>=" + fb.getDepartArrivalHourLower();

            String sqlDepart2hop = "select sour.AIRPORT_CODE as DEPART_AIRPORT, mid.AIRPORT_CODE as FIRST_MID_AIRPORT, dest.AIRPORT_CODE as ARRIVE_AIRPORT, (first_fl.FLIGHT_PRICE + second_fl.FLIGHT_PRICE) as PriceTotal, first_fl.FLIGHT_PRICE as FIRST_FLIGHT_PRICE, second_fl.FLIGHT_PRICE as SECOND_FLIGHT_PRICE, " +
                    "first_fl.FLIGHT_ID as FIRST_FLIGHT_ID, sour.AIRPORT_CITY as DEPART_AIRPORT_CITY, sour.AIRPORT_STATE as DEPART_AIRPORT_STATE, " +
                     "mid.AIRPORT_CITY as MID_AIRPORT_CITY, mid.AIRPORT_STATE as MID_AIRPORT_STATE, " +
                     "first_al.AIRLINE_NAME as FIRST_AIRLINE_NAME, first_al.AIRLINE_ABBREVIATION as FIRST_AIRLINE_ABBREVIATION, first_fl.FLIGHT_NUMBER as FIRST_FLIGHT_NUMBER, " +
                     "to_char(first_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_DEPARTURE_TIME, " +
                     "to_char(first_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_ARRIVAL_TIME, " +
                     "second_fl.FLIGHT_ID as SECOND_FLIGHT_ID, dest.AIRPORT_CITY as ARRIVE_AIRPORT_CITY, dest.AIRPORT_STATE as ARRIVE_AIRPORT_STATE, " +
                     "second_al.AIRLINE_NAME as SECOND_AIRLINE_NAME, second_al.AIRLINE_ABBREVIATION as SECOND_AIRLINE_ABBREVIATION, " +
                     "second_fl.FLIGHT_NUMBER as SECOND_FLIGHT_NUMBER, to_char(second_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_DEPARTURE_TIME, " +
                     "to_char(second_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_ARRIVAL_TIME " +
                     "from AIRPORT sour, AIRPORT mid, AIRPORT dest, AIRLINE first_al, AIRLINE second_al, FLIGHT first_fl, FLIGHT second_fl " +
                     "where sour.AIRPORT_CITY='" + fb.getDepartCity() + "' and dest.AIRPORT_CITY='" + fb.getArriveCity() + "' and sour.AIRPORT_ID=first_fl.DEPART_FROM and " +
                     "dest.AIRPORT_ID=second_fl.ARRIVE_AT and first_fl.ARRIVE_AT=second_fl.DEPART_FROM and first_fl.ARRIVE_AT=mid.AIRPORT_ID " +
                     "and first_al.AIRLINE_ID=first_fl.AIRLINE_ID and  second_al.AIRLINE_ID=second_fl.AIRLINE_ID and " +
                     "EXTRACT(YEAR FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartYear() + " and " +
                     "EXTRACT(MONTH FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartMonth() + " and " +
                     "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartDay() + " and " +
                     "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + fb.getDepartHourUpper() + " and " +
                     "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + fb.getDepartHourLower() + " and " +
                     "EXTRACT(HOUR FROM second_fl.FLIGHT_ARRIVAL_TIME)<=" + fb.getDepartArrivalHourUpper() + " and " +
                     "EXTRACT(HOUR FROM second_fl.FLIGHT_ARRIVAL_TIME)>=" + fb.getDepartArrivalHourLower() + " and " +
                     "second_fl.FLIGHT_DEPARTURE_TIME> first_fl.FLIGHT_ARRIVAL_TIME AND second_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day";

            String sqlDepart3hop = "select sour.AIRPORT_CODE as DEPART_AIRPORT, first_mid.AIRPORT_CODE as FIRST_MID_AIRPORT, second_mid.AIRPORT_CODE as SECOND_MID_AIRPORT, dest.AIRPORT_CODE as ARRIVE_AIRPORT, (first_fl.FLIGHT_PRICE + second_fl.FLIGHT_PRICE + third_fl.FLIGHT_PRICE) as PriceTotal, first_fl.FLIGHT_PRICE as FIRST_FLIGHT_PRICE,second_fl.FLIGHT_PRICE as SECOND_FLIGHT_PRICE, third_fl.FLIGHT_PRICE as THIRD_FLIGHT_PRICE, " +
                    "first_fl.FLIGHT_ID as FIRST_FLIGHT_ID, second_fl.FLIGHT_ID as SECOND_FLIGHT_ID, third_fl.FLIGHT_ID as THIRD_FLIGHT_ID, sour.AIRPORT_CITY as DEPART_AIRPORT_CITY, sour.AIRPORT_STATE as DEPART_AIRPORT_STATE, " +
                      "first_al.AIRLINE_NAME as FIRST_AIRLINE_NAME, first_al.AIRLINE_ABBREVIATION as FIRST_AIRLINE_ABBREVIATION, first_fl.FLIGHT_NUMBER as FIRST_FLIGHT_NUMBER, " +
                      "to_char(first_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(first_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_ARRIVAL_TIME, " +
                      "first_mid.AIRPORT_CITY as FIRST_MID_AIRPORT_CITY, first_mid.AIRPORT_STATE as FIRST_MID_AIRPORT_STATE, " +
                      "second_mid.AIRPORT_CITY as SECOND_MID_AIRPORT_CITY, second_mid.AIRPORT_STATE as SECOND_MID_AIRPORT_STATE, " +
                      "second_al.AIRLINE_NAME as SECOND_AIRLINE_NAME, second_al.AIRLINE_ABBREVIATION as SECOND_AIRLINE_ABBREVIATION, second_fl.FLIGHT_NUMBER as SECOND_FLIGHT_NUMBER, " +
                      "to_char(second_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(second_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_ARRIVAL_TIME, " +
                      "dest.AIRPORT_CITY as ARRIVE_AIRPORT_CITY, dest.AIRPORT_STATE as ARRIVE_AIRPORT_STATE, third_al.AIRLINE_NAME as THIRD_AIRLINE_NAME, " +
                      "third_al.AIRLINE_ABBREVIATION as THIRD_AIRLINE_ABBREVIATION, third_fl.FLIGHT_NUMBER as THIRD_FLIGHT_NUMBER, " +
                      "to_char(third_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(third_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_ARRIVAL_TIME " +
                      "from AIRPORT sour, AIRPORT first_mid, AIRPORT second_mid, AIRPORT dest, AIRLINE first_al, AIRLINE second_al, AIRLINE third_al, FLIGHT first_fl, " +
                      "FLIGHT second_fl, FLIGHT third_fl " +
                      "where sour.AIRPORT_CITY='" + fb.getDepartCity() + "' and dest.AIRPORT_CITY='" + fb.getArriveCity() + "' and sour.AIRPORT_ID=first_fl.DEPART_FROM and " +
                      "dest.AIRPORT_ID=third_fl.ARRIVE_AT and first_fl.ARRIVE_AT=second_fl.DEPART_FROM and first_fl.ARRIVE_AT=first_mid.AIRPORT_ID and " +
                      "second_fl.ARRIVE_AT=third_fl.DEPART_FROM and second_fl.ARRIVE_AT=second_mid.AIRPORT_ID and first_al.AIRLINE_ID=first_fl.AIRLINE_ID and " +
                      "second_al.AIRLINE_ID=second_fl.AIRLINE_ID and third_al.AIRLINE_ID=third_fl.AIRLINE_ID and first_mid.AIRPORT_CITY<>dest.AIRPORT_CITY and sour.AIRPORT_CITY<>second_mid.AIRPORT_CITY and " +
                      "EXTRACT(YEAR FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartYear() + " and " +
                      "EXTRACT(MONTH FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartMonth() + " and " +
                      "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartDay() + " and " +
                      "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + fb.getDepartHourUpper() + " and " +
                      "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + fb.getDepartHourLower() + " and " +
                      "EXTRACT(HOUR FROM third_fl.FLIGHT_ARRIVAL_TIME)<=" + fb.getDepartArrivalHourUpper() + " and " +
                      "EXTRACT(HOUR FROM third_fl.FLIGHT_ARRIVAL_TIME)>=" + fb.getDepartArrivalHourLower() + " and " +
                      "second_fl.FLIGHT_DEPARTURE_TIME> first_fl.FLIGHT_ARRIVAL_TIME AND second_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day AND " +
                      "third_fl.FLIGHT_DEPARTURE_TIME> second_fl.FLIGHT_ARRIVAL_TIME AND third_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day";

            String sqlDepart4hop = "select sour.AIRPORT_CODE as DEPART_AIRPORT, first_mid.AIRPORT_CODE as FIRST_MID_AIRPORT, second_mid.AIRPORT_CODE as SECOND_MID_AIRPORT, third_mid.AIRPORT_CODE as THIRD_MID_AIRPORT, dest.AIRPORT_CODE as ARRIVE_AIRPORT, (first_fl.FLIGHT_PRICE + second_fl.FLIGHT_PRICE + third_fl.FLIGHT_PRICE + fourth_fl.FLIGHT_PRICE) as PriceTotal, first_fl.FLIGHT_PRICE as FIRST_FLIGHT_PRICE, second_fl.FLIGHT_PRICE as SECOND_FLIGHT_PRICE, third_fl.FLIGHT_PRICE as THIRD_FLIGHT_PRICE, fourth_fl.FLIGHT_PRICE as FOURTH_FLIGHT_PRICE, " +
                    "first_fl.FLIGHT_ID as FIRST_FLIGHT_ID, second_fl.FLIGHT_ID as SECOND_FLIGHT_ID, third_fl.FLIGHT_ID as THIRD_FLIGHT_ID, fourth_fl.FLIGHT_ID as FOURTH_FLIGHT_ID, sour.AIRPORT_CITY as DEPART_AIRPORT_CITY, sour.AIRPORT_STATE as DEPART_AIRPORT_STATE, " +
                    //first
                      "first_al.AIRLINE_NAME as FIRST_AIRLINE_NAME, first_al.AIRLINE_ABBREVIATION as FIRST_AIRLINE_ABBREVIATION, first_fl.FLIGHT_NUMBER as FIRST_FLIGHT_NUMBER, " +
                      "to_char(first_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(first_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_ARRIVAL_TIME, " +
                      //three mid airports
                      "first_mid.AIRPORT_CITY as FIRST_MID_AIRPORT_CITY, first_mid.AIRPORT_STATE as FIRST_MID_AIRPORT_STATE, " +
                      "second_mid.AIRPORT_CITY as SECOND_MID_AIRPORT_CITY, second_mid.AIRPORT_STATE as SECOND_MID_AIRPORT_STATE, " +
                      "third_mid.AIRPORT_CITY as THIRD_MID_AIRPORT_CITY, third_mid.AIRPORT_STATE as THIRD_MID_AIRPORT_STATE, " +
                      //second
                      "second_al.AIRLINE_NAME as SECOND_AIRLINE_NAME, second_al.AIRLINE_ABBREVIATION as SECOND_AIRLINE_ABBREVIATION, second_fl.FLIGHT_NUMBER as SECOND_FLIGHT_NUMBER, " +
                      "to_char(second_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(second_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_ARRIVAL_TIME, " +
                      //
                      "dest.AIRPORT_CITY as ARRIVE_AIRPORT_CITY, dest.AIRPORT_STATE as ARRIVE_AIRPORT_STATE, third_al.AIRLINE_NAME as THIRD_AIRLINE_NAME, " +
                      //third
                      "third_al.AIRLINE_ABBREVIATION as THIRD_AIRLINE_ABBREVIATION, third_fl.FLIGHT_NUMBER as THIRD_FLIGHT_NUMBER, " +
                      "to_char(third_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(third_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_ARRIVAL_TIME, " +
                      //fourth
                      "fourth_al.AIRLINE_NAME as FOURTH_AIRLINE_NAME, fourth_al.AIRLINE_ABBREVIATION as FOURTH_AIRLINE_ABBREVIATION, fourth_fl.FLIGHT_NUMBER as FOURTH_FLIGHT_NUMBER, " +
                      "to_char(fourth_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FOURTH_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(fourth_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FOURTH_FLIGHT_ARRIVAL_TIME " +
                      //up to this point
                      "from AIRPORT sour, AIRPORT first_mid, AIRPORT second_mid, AIRPORT third_mid, AIRPORT dest, AIRLINE first_al, AIRLINE second_al, AIRLINE third_al, AIRLINE fourth_al, FLIGHT first_fl, " +
                      "FLIGHT second_fl, FLIGHT third_fl, FLIGHT fourth_fl " +
                      "where sour.AIRPORT_CITY='" + fb.getDepartCity() + "' and dest.AIRPORT_CITY='" + fb.getArriveCity() + "' and sour.AIRPORT_ID=first_fl.DEPART_FROM and " +
                      "dest.AIRPORT_ID=fourth_fl.ARRIVE_AT and first_fl.ARRIVE_AT=second_fl.DEPART_FROM and first_fl.ARRIVE_AT=first_mid.AIRPORT_ID and " +
                      "second_fl.ARRIVE_AT=third_fl.DEPART_FROM and second_fl.ARRIVE_AT=second_mid.AIRPORT_ID and first_al.AIRLINE_ID=first_fl.AIRLINE_ID and " +
                      "second_al.AIRLINE_ID=second_fl.AIRLINE_ID and third_al.AIRLINE_ID=third_fl.AIRLINE_ID and fourth_al.AIRLINE_ID=fourth_fl.AIRLINE_ID and third_fl.ARRIVE_AT=fourth_fl.DEPART_FROM and third_fl.ARRIVE_AT=third_mid.AIRPORT_ID and " +
                      "first_mid.AIRPORT_CITY<>dest.AIRPORT_CITY and first_mid.AIRPORT_CITY<>third_mid.AIRPORT_CITY and sour.AIRPORT_CITY<>second_mid.AIRPORT_CITY and sour.AIRPORT_CITY<>third_mid.AIRPORT_CITY and dest.AIRPORT_CITY<>second_mid.AIRPORT_CITY and dest.AIRPORT_CITY<>third_mid.AIRPORT_CITY and " +
                      "EXTRACT(YEAR FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartYear() + " and " +
                      "EXTRACT(MONTH FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartMonth() + " and " +
                      "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartDay() + " and " +
                      "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + fb.getDepartHourUpper() + " and " +
                      "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + fb.getDepartHourLower() + " and " +
                      "EXTRACT(HOUR FROM fourth_fl.FLIGHT_ARRIVAL_TIME)<=" + fb.getDepartArrivalHourUpper() + " and " +
                      "EXTRACT(HOUR FROM fourth_fl.FLIGHT_ARRIVAL_TIME)>=" + fb.getDepartArrivalHourLower() + " and " +
                      "second_fl.FLIGHT_DEPARTURE_TIME> first_fl.FLIGHT_ARRIVAL_TIME AND second_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day AND " +
                      "third_fl.FLIGHT_DEPARTURE_TIME> second_fl.FLIGHT_ARRIVAL_TIME AND third_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day and " +
                      "fourth_fl.FLIGHT_DEPARTURE_TIME> third_fl.FLIGHT_ARRIVAL_TIME AND fourth_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day";

            String sqlDepart5hop = "select sour.AIRPORT_CODE as DEPART_AIRPORT, first_mid.AIRPORT_CODE as FIRST_MID_AIRPORT, second_mid.AIRPORT_CODE as SECOND_MID_AIRPORT, third_mid.AIRPORT_CODE as THIRD_MID_AIRPORT, fourth_mid.AIRPORT_CODE as FOURTH_MID_AIRPORT, dest.AIRPORT_CODE as ARRIVE_AIRPORT, (first_fl.FLIGHT_PRICE + second_fl.FLIGHT_PRICE + third_fl.FLIGHT_PRICE + fourth_fl.FLIGHT_PRICE + fifth_fl.FLIGHT_PRICE) as PriceTotal ,first_fl.FLIGHT_ID as FIRST_FLIGHT_ID, second_fl.FLIGHT_ID as SECOND_FLIGHT_ID, third_fl.FLIGHT_ID as THIRD_FLIGHT_ID, fourth_fl.FLIGHT_ID as FOURTH_FLIGHT_ID, fifth_fl.FLIGHT_ID as FIFTH_FLIGHT_ID, sour.AIRPORT_CITY as DEPART_AIRPORT_CITY, sour.AIRPORT_STATE as DEPART_AIRPORT_STATE," +
                    "first_al.AIRLINE_NAME as FIRST_AIRLINE_NAME, first_al.AIRLINE_ABBREVIATION as FIRST_AIRLINE_ABBREVIATION, first_fl.FLIGHT_NUMBER as FIRST_FLIGHT_NUMBER," +
                    "to_char(first_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_DEPARTURE_TIME," +
                    "to_char(first_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_ARRIVAL_TIME," +
                    "first_mid.AIRPORT_CITY as FIRST_MID_AIRPORT_CITY, first_mid.AIRPORT_STATE as FIRST_MID_AIRPORT_STATE," +
                    "second_mid.AIRPORT_CITY as SECOND_MID_AIRPORT_CITY, second_mid.AIRPORT_STATE as SECOND_MID_AIRPORT_STATE," +
                    "third_mid.AIRPORT_CITY as THIRD_MID_AIRPORT_CITY, third_mid.AIRPORT_STATE as THIRD_MID_AIRPORT_STATE,"+
                    "fourth_mid.AIRPORT_CITY as FOURTH_MID_AIRPORT_CITY, fourth_mid.AIRPORT_STATE as FOURTH_MID_AIRPORT_STATE,"+
                    "second_al.AIRLINE_NAME as SECOND_AIRLINE_NAME, second_al.AIRLINE_ABBREVIATION as SECOND_AIRLINE_ABBREVIATION, second_fl.FLIGHT_NUMBER as SECOND_FLIGHT_NUMBER,"+
                    "to_char(second_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_DEPARTURE_TIME,"+
                    "to_char(second_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_ARRIVAL_TIME,"+
                    "dest.AIRPORT_CITY as ARRIVE_AIRPORT_CITY, dest.AIRPORT_STATE as ARRIVE_AIRPORT_STATE, third_al.AIRLINE_NAME as THIRD_AIRLINE_NAME,"+
                    "third_al.AIRLINE_ABBREVIATION as THIRD_AIRLINE_ABBREVIATION, third_fl.FLIGHT_NUMBER as THIRD_FLIGHT_NUMBER,"+
                    "to_char(third_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_DEPARTURE_TIME,"+
                    "to_char(third_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_ARRIVAL_TIME,"+
                    "fourth_al.AIRLINE_NAME as FOURTH_AIRLINE_NAME, fourth_al.AIRLINE_ABBREVIATION as FOURTH_AIRLINE_ABBREVIATION, fourth_fl.FLIGHT_NUMBER as FOURTH_FLIGHT_NUMBER,"+
                    "to_char(fourth_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FOURTH_FLIGHT_DEPARTURE_TIME,"+
                    "to_char(fourth_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FOURTH_FLIGHT_ARRIVAL_TIME,"+
                    "fifth_al.AIRLINE_NAME as FIFTH_AIRLINE_NAME, fifth_al.AIRLINE_ABBREVIATION as FIFTH_AIRLINE_ABBREVIATION, fifth_fl.FLIGHT_NUMBER as FIFTH_FLIGHT_NUMBER,"+
                    "to_char(fifth_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FIFTH_FLIGHT_DEPARTURE_TIME,"+
                    "to_char(fifth_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FIFTH_FLIGHT_ARRIVAL_TIME "+
                    "from AIRPORT sour, AIRPORT first_mid, AIRPORT second_mid, AIRPORT third_mid, AIRPORT fourth_mid, AIRPORT dest, AIRLINE first_al, AIRLINE second_al, AIRLINE third_al, AIRLINE fourth_al, AIRLINE fifth_al, FLIGHT first_fl,"+
                    "FLIGHT second_fl, FLIGHT third_fl, FLIGHT fourth_fl, FLIGHT fifth_fl "+
                    "where sour.AIRPORT_CITY='" + fb.getDepartCity() + "' and dest.AIRPORT_CITY='" + fb.getArriveCity() + "' and sour.AIRPORT_ID=first_fl.DEPART_FROM and "+
                    "dest.AIRPORT_ID=fifth_fl.ARRIVE_AT and first_fl.ARRIVE_AT=second_fl.DEPART_FROM and first_fl.ARRIVE_AT=first_mid.AIRPORT_ID and "+
                    "second_fl.ARRIVE_AT=third_fl.DEPART_FROM and second_fl.ARRIVE_AT=second_mid.AIRPORT_ID and first_al.AIRLINE_ID=first_fl.AIRLINE_ID and "+
                    "second_al.AIRLINE_ID=second_fl.AIRLINE_ID and third_al.AIRLINE_ID=third_fl.AIRLINE_ID and fourth_al.AIRLINE_ID=fourth_fl.AIRLINE_ID and fifth_al.AIRLINE_ID=fifth_fl.AIRLINE_ID and third_fl.ARRIVE_AT=fourth_fl.DEPART_FROM and third_fl.ARRIVE_AT=third_mid.AIRPORT_ID and fourth_fl.ARRIVE_AT=fifth_fl.DEPART_FROM and fourth_fl.ARRIVE_AT=fourth_mid.AIRPORT_ID and "+
                    "first_mid.AIRPORT_CITY<>dest.AIRPORT_CITY and first_mid.AIRPORT_CITY<>fourth_mid.AIRPORT_CITY and first_mid.AIRPORT_CITY<>third_mid.AIRPORT_CITY and sour.AIRPORT_CITY<>second_mid.AIRPORT_CITY and sour.AIRPORT_CITY<>third_mid.AIRPORT_CITY and sour.AIRPORT_CITY<>fourth_mid.AIRPORT_CITY and second_mid.AIRPORT_CITY<>fourth_mid.AIRPORT_CITY and dest.AIRPORT_CITY<>second_mid.AIRPORT_CITY and dest.AIRPORT_CITY<>third_mid.AIRPORT_CITY and "+
                    "EXTRACT(YEAR FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartYear() + " and " +
                    "EXTRACT(MONTH FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartMonth() + " and " +
                    "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartDay() + " and " +
                    "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + fb.getDepartHourUpper() + " and " +
                    "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + fb.getDepartHourLower() + " and " +
                    "EXTRACT(HOUR FROM fourth_fl.FLIGHT_ARRIVAL_TIME)<=" + fb.getDepartArrivalHourUpper() + " and " +
                    "EXTRACT(HOUR FROM fourth_fl.FLIGHT_ARRIVAL_TIME)>=" + fb.getDepartArrivalHourLower() + " and " +
                    "second_fl.FLIGHT_DEPARTURE_TIME> first_fl.FLIGHT_ARRIVAL_TIME AND second_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day and "+
                    "third_fl.FLIGHT_DEPARTURE_TIME> second_fl.FLIGHT_ARRIVAL_TIME AND third_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day and "+
                    "fourth_fl.FLIGHT_DEPARTURE_TIME> third_fl.FLIGHT_ARRIVAL_TIME AND fourth_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day and "+
                    "fifth_fl.FLIGHT_DEPARTURE_TIME> fourth_fl.FLIGHT_ARRIVAL_TIME AND fifth_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day";

            String sqlReturn1hop = "select ap.AIRPORT_CODE as DEPART_AIRPORT, a.AIRPORT_CODE as ARRIVE_AIRPORT, fl.FLIGHT_PRICE as PriceTotal, fl.FLIGHT_PRICE as FIRST_FLIGHT_PRICE, fl.FLIGHT_ID as FIRST_FLIGHT_ID, ap.AIRPORT_CITY as DEPART_AIRPORT_CITY, ap.AIRPORT_STATE as DEPART_AIRPORT_STATE, " +
                    "a.AIRPORT_CITY as ARRIVE_AIRPORT_CITY, a.AIRPORT_STATE as ARRIVE_AIRPORT_STATE, " +
                    "al.AIRLINE_NAME, al.AIRLINE_ABBREVIATION, fl.FLIGHT_NUMBER, to_char(fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FLIGHT_DEPARTURE_TIME, " +
                    "to_char(fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FLIGHT_ARRIVAL_TIME from " +
                    "AIRPORT ap, AIRPORT a, AIRLINE al, FLIGHT fl where " +
                    "ap.AIRPORT_CITY='" + fb.getArriveCity() + "' and ap.AIRPORT_ID=fl.DEPART_FROM and " +
                    "al.AIRLINE_ID=fl.AIRLINE_ID and a.AIRPORT_CITY='" + fb.getDepartCity() + "' and " +
                    "a.AIRPORT_ID=fl.ARRIVE_AT and EXTRACT(YEAR FROM " +
                    "fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnYear() + " and EXTRACT(MONTH FROM " +
                    "fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnMonth() + " and EXTRACT(DAY FROM " +
                    "fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnDay() + " and EXTRACT(HOUR FROM " +
                    "fl.FLIGHT_DEPARTURE_TIME)<=" + fb.getReturnHourUpper() + " and EXTRACT(HOUR FROM " +
                    "fl.FLIGHT_DEPARTURE_TIME)>=" + fb.getReturnHourLower() +
                    " and EXTRACT(HOUR FROM fl.FLIGHT_ARRIVAL_TIME)<=" + fb.getReturnArrivalHourUpper() +
                    " and EXTRACT(HOUR FROM fl.FLIGHT_ARRIVAL_TIME)>=" + fb.getReturnArrivalHourLower();

            String sqlReturn2hop = "select sour.AIRPORT_CODE as DEPART_AIRPORT, mid.AIRPORT_CODE as FIRST_MID_AIRPORT, dest.AIRPORT_CODE as ARRIVE_AIRPORT, (first_fl.FLIGHT_PRICE + second_fl.FLIGHT_PRICE) as PriceTotal, first_fl.FLIGHT_PRICE as FIRST_FLIGHT_PRICE, second_fl.FLIGHT_PRICE as SECOND_FLIGHT_PRICE, " +
                    "first_fl.FLIGHT_ID as FIRST_FLIGHT_ID, sour.AIRPORT_CITY as DEPART_AIRPORT_CITY, sour.AIRPORT_STATE as DEPART_AIRPORT_STATE, " +
                     "mid.AIRPORT_CITY as MID_AIRPORT_CITY, mid.AIRPORT_STATE as MID_AIRPORT_STATE, " +
                     "first_al.AIRLINE_NAME as FIRST_AIRLINE_NAME, first_al.AIRLINE_ABBREVIATION as FIRST_AIRLINE_ABBREVIATION, first_fl.FLIGHT_NUMBER as FIRST_FLIGHT_NUMBER, " +
                     "to_char(first_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_DEPARTURE_TIME, " +
                     "to_char(first_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_ARRIVAL_TIME, " +
                     "second_fl.FLIGHT_ID as SECOND_FLIGHT_ID, dest.AIRPORT_CITY as ARRIVE_AIRPORT_CITY, dest.AIRPORT_STATE as ARRIVE_AIRPORT_STATE, " +
                     "second_al.AIRLINE_NAME as SECOND_AIRLINE_NAME, second_al.AIRLINE_ABBREVIATION as SECOND_AIRLINE_ABBREVIATION, " +
                     "second_fl.FLIGHT_NUMBER as SECOND_FLIGHT_NUMBER, to_char(second_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_DEPARTURE_TIME, " +
                     "to_char(second_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_ARRIVAL_TIME " +
                     "from AIRPORT sour, AIRPORT mid, AIRPORT dest, AIRLINE first_al, AIRLINE second_al, FLIGHT first_fl, FLIGHT second_fl " +
                     "where sour.AIRPORT_CITY='" + fb.getArriveCity() + "' and dest.AIRPORT_CITY='" + fb.getDepartCity() + "' and sour.AIRPORT_ID=first_fl.DEPART_FROM and " +
                     "dest.AIRPORT_ID=second_fl.ARRIVE_AT and first_fl.ARRIVE_AT=second_fl.DEPART_FROM and first_fl.ARRIVE_AT=mid.AIRPORT_ID " +
                     "and first_al.AIRLINE_ID=first_fl.AIRLINE_ID and  second_al.AIRLINE_ID=second_fl.AIRLINE_ID and " +
                     "EXTRACT(YEAR FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnYear() + " and " +
                     "EXTRACT(MONTH FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnMonth() + " and " +
                     "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnDay() + " and " +
                     "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + fb.getReturnHourUpper() + " and " +
                     "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + fb.getReturnHourLower() + " and " +
                     "EXTRACT(HOUR FROM second_fl.FLIGHT_ARRIVAL_TIME)<=" + fb.getReturnArrivalHourUpper() + " and " +
                     "EXTRACT(HOUR FROM second_fl.FLIGHT_ARRIVAL_TIME)>=" + fb.getReturnArrivalHourLower() + " and " +
                     "second_fl.FLIGHT_DEPARTURE_TIME> first_fl.FLIGHT_ARRIVAL_TIME AND second_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day";

            String sqlReturn3hop = "select sour.AIRPORT_CODE as DEPART_AIRPORT, first_mid.AIRPORT_CODE as FIRST_MID_AIRPORT, second_mid.AIRPORT_CODE as SECOND_MID_AIRPORT, dest.AIRPORT_CODE as ARRIVE_AIRPORT, (first_fl.FLIGHT_PRICE + second_fl.FLIGHT_PRICE + third_fl.FLIGHT_PRICE) as PriceTotal, first_fl.FLIGHT_PRICE as FIRST_FLIGHT_PRICE,second_fl.FLIGHT_PRICE as SECOND_FLIGHT_PRICE, third_fl.FLIGHT_PRICE as THIRD_FLIGHT_PRICE, " +
                    "first_fl.FLIGHT_ID as FIRST_FLIGHT_ID, second_fl.FLIGHT_ID as SECOND_FLIGHT_ID, third_fl.FLIGHT_ID as THIRD_FLIGHT_ID, sour.AIRPORT_CITY as DEPART_AIRPORT_CITY, sour.AIRPORT_STATE as DEPART_AIRPORT_STATE, " +
                      "first_al.AIRLINE_NAME as FIRST_AIRLINE_NAME, first_al.AIRLINE_ABBREVIATION as FIRST_AIRLINE_ABBREVIATION, first_fl.FLIGHT_NUMBER as FIRST_FLIGHT_NUMBER, " +
                      "to_char(first_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(first_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_ARRIVAL_TIME, " +
                      "first_mid.AIRPORT_CITY as FIRST_MID_AIRPORT_CITY, first_mid.AIRPORT_STATE as FIRST_MID_AIRPORT_STATE, " +
                      "second_mid.AIRPORT_CITY as SECOND_MID_AIRPORT_CITY, second_mid.AIRPORT_STATE as SECOND_MID_AIRPORT_STATE, " +
                      "second_al.AIRLINE_NAME as SECOND_AIRLINE_NAME, second_al.AIRLINE_ABBREVIATION as SECOND_AIRLINE_ABBREVIATION, second_fl.FLIGHT_NUMBER as SECOND_FLIGHT_NUMBER, " +
                      "to_char(second_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(second_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_ARRIVAL_TIME, " +
                      "dest.AIRPORT_CITY as ARRIVE_AIRPORT_CITY, dest.AIRPORT_STATE as ARRIVE_AIRPORT_STATE, third_al.AIRLINE_NAME as THIRD_AIRLINE_NAME, " +
                      "third_al.AIRLINE_ABBREVIATION as THIRD_AIRLINE_ABBREVIATION, third_fl.FLIGHT_NUMBER as THIRD_FLIGHT_NUMBER, " +
                      "to_char(third_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(third_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_ARRIVAL_TIME " +
                      "from AIRPORT sour, AIRPORT first_mid, AIRPORT second_mid, AIRPORT dest, AIRLINE first_al, AIRLINE second_al, AIRLINE third_al, FLIGHT first_fl, " +
                      "FLIGHT second_fl, FLIGHT third_fl " +
                      "where sour.AIRPORT_CITY='" + fb.getArriveCity() + "' and dest.AIRPORT_CITY='" + fb.getDepartCity() + "' and sour.AIRPORT_ID=first_fl.DEPART_FROM and " +
                      "dest.AIRPORT_ID=third_fl.ARRIVE_AT and first_fl.ARRIVE_AT=second_fl.DEPART_FROM and first_fl.ARRIVE_AT=first_mid.AIRPORT_ID and " +
                      "second_fl.ARRIVE_AT=third_fl.DEPART_FROM and second_fl.ARRIVE_AT=second_mid.AIRPORT_ID and first_al.AIRLINE_ID=first_fl.AIRLINE_ID and " +
                      "second_al.AIRLINE_ID=second_fl.AIRLINE_ID and third_al.AIRLINE_ID=third_fl.AIRLINE_ID and first_mid.AIRPORT_CITY<>dest.AIRPORT_CITY and sour.AIRPORT_CITY<>second_mid.AIRPORT_CITY and " +
                      "EXTRACT(YEAR FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnYear() + " and " +
                      "EXTRACT(MONTH FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnMonth() + " and " +
                      "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnDay() + " and " +
                      "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + fb.getReturnHourUpper() + " and " +
                      "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + fb.getReturnHourLower() + " and " +
                      "EXTRACT(HOUR FROM third_fl.FLIGHT_ARRIVAL_TIME)<=" + fb.getReturnArrivalHourUpper() + " and " +
                     "EXTRACT(HOUR FROM third_fl.FLIGHT_ARRIVAL_TIME)>=" + fb.getReturnArrivalHourLower() + " and " +
                      "second_fl.FLIGHT_DEPARTURE_TIME> first_fl.FLIGHT_ARRIVAL_TIME AND second_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day AND " +
                      "third_fl.FLIGHT_DEPARTURE_TIME> second_fl.FLIGHT_ARRIVAL_TIME AND third_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day";

            // we have airline, airport and flight as tables
            String sqlReturn4hop = "select sour.AIRPORT_CODE as DEPART_AIRPORT, first_mid.AIRPORT_CODE as FIRST_MID_AIRPORT, second_mid.AIRPORT_CODE as SECOND_MID_AIRPORT, third_mid.AIRPORT_CODE as THIRD_MID_AIRPORT, dest.AIRPORT_CODE as ARRIVE_AIRPORT, (first_fl.FLIGHT_PRICE + second_fl.FLIGHT_PRICE + third_fl.FLIGHT_PRICE + fourth_fl.FLIGHT_PRICE) as PriceTotal, first_fl.FLIGHT_PRICE as FIRST_FLIGHT_PRICE, second_fl.FLIGHT_PRICE as SECOND_FLIGHT_PRICE, third_fl.FLIGHT_PRICE as THIRD_FLIGHT_PRICE, fourth_fl.FLIGHT_PRICE as FOURTH_FLIGHT_PRICE, " +
                    "first_fl.FLIGHT_ID as FIRST_FLIGHT_ID, second_fl.FLIGHT_ID as SECOND_FLIGHT_ID, third_fl.FLIGHT_ID as THIRD_FLIGHT_ID, fourth_fl.FLIGHT_ID as FOURTH_FLIGHT_ID, sour.AIRPORT_CITY as DEPART_AIRPORT_CITY, sour.AIRPORT_STATE as DEPART_AIRPORT_STATE, " +
                      "first_al.AIRLINE_NAME as FIRST_AIRLINE_NAME, first_al.AIRLINE_ABBREVIATION as FIRST_AIRLINE_ABBREVIATION, first_fl.FLIGHT_NUMBER as FIRST_FLIGHT_NUMBER, " +
                      "to_char(first_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(first_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_ARRIVAL_TIME, " +
                      "first_mid.AIRPORT_CITY as FIRST_MID_AIRPORT_CITY, first_mid.AIRPORT_STATE as FIRST_MID_AIRPORT_STATE, " +
                      "second_mid.AIRPORT_CITY as SECOND_MID_AIRPORT_CITY, second_mid.AIRPORT_STATE as SECOND_MID_AIRPORT_STATE, " +
                      "third_mid.AIRPORT_CITY as THIRD_MID_AIRPORT_CITY, third_mid.AIRPORT_STATE as THIRD_MID_AIRPORT_STATE, " +
                      "second_al.AIRLINE_NAME as SECOND_AIRLINE_NAME, second_al.AIRLINE_ABBREVIATION as SECOND_AIRLINE_ABBREVIATION, second_fl.FLIGHT_NUMBER as SECOND_FLIGHT_NUMBER, " +
                      "to_char(second_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(second_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_ARRIVAL_TIME, " +
                      "dest.AIRPORT_CITY as ARRIVE_AIRPORT_CITY, dest.AIRPORT_STATE as ARRIVE_AIRPORT_STATE, third_al.AIRLINE_NAME as THIRD_AIRLINE_NAME, " +
                      "third_al.AIRLINE_ABBREVIATION as THIRD_AIRLINE_ABBREVIATION, third_fl.FLIGHT_NUMBER as THIRD_FLIGHT_NUMBER, " +
                      "to_char(third_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(third_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_ARRIVAL_TIME, " +
                      "fourth_al.AIRLINE_NAME as FOURTH_AIRLINE_NAME, fourth_al.AIRLINE_ABBREVIATION as FOURTH_AIRLINE_ABBREVIATION, fourth_fl.FLIGHT_NUMBER as FOURTH_FLIGHT_NUMBER, " +
                      "to_char(fourth_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FOURTH_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(fourth_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FOURTH_FLIGHT_ARRIVAL_TIME " +
                      "from AIRPORT sour, AIRPORT first_mid, AIRPORT second_mid, AIRPORT third_mid, AIRPORT dest, AIRLINE first_al, AIRLINE second_al, AIRLINE third_al, AIRLINE fourth_al, FLIGHT first_fl, " +
                      "FLIGHT second_fl, FLIGHT third_fl, FLIGHT fourth_fl " +
                      "where sour.AIRPORT_CITY='" + fb.getArriveCity() + "' and dest.AIRPORT_CITY='" + fb.getDepartCity() + "' and sour.AIRPORT_ID=first_fl.DEPART_FROM and " +
                      "dest.AIRPORT_ID=fourth_fl.ARRIVE_AT and first_fl.ARRIVE_AT=second_fl.DEPART_FROM and first_fl.ARRIVE_AT=first_mid.AIRPORT_ID and " +
                      "second_fl.ARRIVE_AT=third_fl.DEPART_FROM and second_fl.ARRIVE_AT=second_mid.AIRPORT_ID and first_al.AIRLINE_ID=first_fl.AIRLINE_ID and " +
                      "second_al.AIRLINE_ID=second_fl.AIRLINE_ID and third_al.AIRLINE_ID=third_fl.AIRLINE_ID and fourth_al.AIRLINE_ID=fourth_fl.AIRLINE_ID and third_fl.ARRIVE_AT=fourth_fl.DEPART_FROM and third_fl.ARRIVE_AT=third_mid.AIRPORT_ID and " +
                      "first_mid.AIRPORT_CITY<>dest.AIRPORT_CITY and first_mid.AIRPORT_CITY<>third_mid.AIRPORT_CITY and sour.AIRPORT_CITY<>second_mid.AIRPORT_CITY and sour.AIRPORT_CITY<>third_mid.AIRPORT_CITY and dest.AIRPORT_CITY<>second_mid.AIRPORT_CITY and dest.AIRPORT_CITY<>third_mid.AIRPORT_CITY and " +
                      "EXTRACT(YEAR FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnYear() + " and " +
                      "EXTRACT(MONTH FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnMonth() + " and " +
                      "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnDay() + " and " +
                      "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + fb.getReturnHourUpper() + " and " +
                      "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + fb.getReturnHourLower() + " and " +
                      "EXTRACT(HOUR FROM fourth_fl.FLIGHT_ARRIVAL_TIME)<=" + fb.getReturnArrivalHourUpper() + " and " +
                     "EXTRACT(HOUR FROM fourth_fl.FLIGHT_ARRIVAL_TIME)>=" + fb.getReturnArrivalHourLower() + " and " +
                      "second_fl.FLIGHT_DEPARTURE_TIME> first_fl.FLIGHT_ARRIVAL_TIME AND second_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day AND " +
                      "third_fl.FLIGHT_DEPARTURE_TIME> second_fl.FLIGHT_ARRIVAL_TIME AND third_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day and " +
                      "fourth_fl.FLIGHT_DEPARTURE_TIME> third_fl.FLIGHT_ARRIVAL_TIME AND fourth_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day";

            String sqlReturn5hop = "select sour.AIRPORT_CODE as DEPART_AIRPORT, first_mid.AIRPORT_CODE as FIRST_MID_AIRPORT, second_mid.AIRPORT_CODE as SECOND_MID_AIRPORT, third_mid.AIRPORT_CODE as THIRD_MID_AIRPORT, fourth_mid.AIRPORT_CODE as FOURTH_MID_AIRPORT, dest.AIRPORT_CODE as ARRIVE_AIRPORT, (first_fl.FLIGHT_PRICE + second_fl.FLIGHT_PRICE + third_fl.FLIGHT_PRICE + fourth_fl.FLIGHT_PRICE + fifth_fl.FLIGHT_PRICE) as PriceTotal ,first_fl.FLIGHT_ID as FIRST_FLIGHT_ID, second_fl.FLIGHT_ID as SECOND_FLIGHT_ID, third_fl.FLIGHT_ID as THIRD_FLIGHT_ID, fourth_fl.FLIGHT_ID as FOURTH_FLIGHT_ID, fifth_fl.FLIGHT_ID as FIFTH_FLIGHT_ID, sour.AIRPORT_CITY as DEPART_AIRPORT_CITY, sour.AIRPORT_STATE as DEPART_AIRPORT_STATE," +
                    "first_al.AIRLINE_NAME as FIRST_AIRLINE_NAME, first_al.AIRLINE_ABBREVIATION as FIRST_AIRLINE_ABBREVIATION, first_fl.FLIGHT_NUMBER as FIRST_FLIGHT_NUMBER," +
                    "to_char(first_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_DEPARTURE_TIME," +
                    "to_char(first_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_ARRIVAL_TIME," +
                    "first_mid.AIRPORT_CITY as FIRST_MID_AIRPORT_CITY, first_mid.AIRPORT_STATE as FIRST_MID_AIRPORT_STATE," +
                    "second_mid.AIRPORT_CITY as SECOND_MID_AIRPORT_CITY, second_mid.AIRPORT_STATE as SECOND_MID_AIRPORT_STATE," +
                    "third_mid.AIRPORT_CITY as THIRD_MID_AIRPORT_CITY, third_mid.AIRPORT_STATE as THIRD_MID_AIRPORT_STATE,"+
                    "fourth_mid.AIRPORT_CITY as FOURTH_MID_AIRPORT_CITY, fourth_mid.AIRPORT_STATE as FOURTH_MID_AIRPORT_STATE,"+
                    "second_al.AIRLINE_NAME as SECOND_AIRLINE_NAME, second_al.AIRLINE_ABBREVIATION as SECOND_AIRLINE_ABBREVIATION, second_fl.FLIGHT_NUMBER as SECOND_FLIGHT_NUMBER,"+
                    "to_char(second_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_DEPARTURE_TIME,"+
                    "to_char(second_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_ARRIVAL_TIME,"+
                    "dest.AIRPORT_CITY as ARRIVE_AIRPORT_CITY, dest.AIRPORT_STATE as ARRIVE_AIRPORT_STATE, third_al.AIRLINE_NAME as THIRD_AIRLINE_NAME,"+
                    "third_al.AIRLINE_ABBREVIATION as THIRD_AIRLINE_ABBREVIATION, third_fl.FLIGHT_NUMBER as THIRD_FLIGHT_NUMBER,"+
                    "to_char(third_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_DEPARTURE_TIME,"+
                    "to_char(third_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_ARRIVAL_TIME,"+
                    "fourth_al.AIRLINE_NAME as FOURTH_AIRLINE_NAME, fourth_al.AIRLINE_ABBREVIATION as FOURTH_AIRLINE_ABBREVIATION, fourth_fl.FLIGHT_NUMBER as FOURTH_FLIGHT_NUMBER,"+
                    "to_char(fourth_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FOURTH_FLIGHT_DEPARTURE_TIME,"+
                    "to_char(fourth_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FOURTH_FLIGHT_ARRIVAL_TIME,"+
                    "fifth_al.AIRLINE_NAME as FIFTH_AIRLINE_NAME, fifth_al.AIRLINE_ABBREVIATION as FIFTH_AIRLINE_ABBREVIATION, fifth_fl.FLIGHT_NUMBER as FIFTH_FLIGHT_NUMBER,"+
                    "to_char(fifth_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FIFTH_FLIGHT_DEPARTURE_TIME,"+
                    "to_char(fifth_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FIFTH_FLIGHT_ARRIVAL_TIME "+
                    "from AIRPORT sour, AIRPORT first_mid, AIRPORT second_mid, AIRPORT third_mid, AIRPORT fourth_mid, AIRPORT dest, AIRLINE first_al, AIRLINE second_al, AIRLINE third_al, AIRLINE fourth_al, AIRLINE fifth_al, FLIGHT first_fl,"+
                    "FLIGHT second_fl, FLIGHT third_fl, FLIGHT fourth_fl, FLIGHT fifth_fl "+
                    "where sour.AIRPORT_CITY='" + fb.getArriveCity() + "' and dest.AIRPORT_CITY='" + fb.getDepartCity() + "' and sour.AIRPORT_ID=first_fl.DEPART_FROM and "+
                    "dest.AIRPORT_ID=fifth_fl.ARRIVE_AT and first_fl.ARRIVE_AT=second_fl.DEPART_FROM and first_fl.ARRIVE_AT=first_mid.AIRPORT_ID and "+
                    "second_fl.ARRIVE_AT=third_fl.DEPART_FROM and second_fl.ARRIVE_AT=second_mid.AIRPORT_ID and first_al.AIRLINE_ID=first_fl.AIRLINE_ID and "+
                    "second_al.AIRLINE_ID=second_fl.AIRLINE_ID and third_al.AIRLINE_ID=third_fl.AIRLINE_ID and fourth_al.AIRLINE_ID=fourth_fl.AIRLINE_ID and fifth_al.AIRLINE_ID=fifth_fl.AIRLINE_ID and third_fl.ARRIVE_AT=fourth_fl.DEPART_FROM and third_fl.ARRIVE_AT=third_mid.AIRPORT_ID and fourth_fl.ARRIVE_AT=fifth_fl.DEPART_FROM and fourth_fl.ARRIVE_AT=fourth_mid.AIRPORT_ID and "+
                    "first_mid.AIRPORT_CITY<>dest.AIRPORT_CITY and first_mid.AIRPORT_CITY<>fourth_mid.AIRPORT_CITY and first_mid.AIRPORT_CITY<>third_mid.AIRPORT_CITY and sour.AIRPORT_CITY<>second_mid.AIRPORT_CITY and sour.AIRPORT_CITY<>third_mid.AIRPORT_CITY and sour.AIRPORT_CITY<>fourth_mid.AIRPORT_CITY and second_mid.AIRPORT_CITY<>fourth_mid.AIRPORT_CITY and dest.AIRPORT_CITY<>second_mid.AIRPORT_CITY and dest.AIRPORT_CITY<>third_mid.AIRPORT_CITY and "+
                    "EXTRACT(YEAR FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnYear() + " and " +
                    "EXTRACT(MONTH FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnMonth() + " and " +
                    "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnDay() + " and " +
                    "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + fb.getReturnHourUpper() + " and " +
                    "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + fb.getReturnHourLower() + " and " +
                    "EXTRACT(HOUR FROM fourth_fl.FLIGHT_ARRIVAL_TIME)<=" + fb.getReturnArrivalHourUpper() + " and " +
                    "EXTRACT(HOUR FROM fourth_fl.FLIGHT_ARRIVAL_TIME)>=" + fb.getReturnArrivalHourLower() + " and " +
                    "second_fl.FLIGHT_DEPARTURE_TIME> first_fl.FLIGHT_ARRIVAL_TIME AND second_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day and "+
                    "third_fl.FLIGHT_DEPARTURE_TIME> second_fl.FLIGHT_ARRIVAL_TIME AND third_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day and "+
                    "fourth_fl.FLIGHT_DEPARTURE_TIME> third_fl.FLIGHT_ARRIVAL_TIME AND fourth_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day and "+
                    "fifth_fl.FLIGHT_DEPARTURE_TIME> fourth_fl.FLIGHT_ARRIVAL_TIME AND fifth_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day";


            if (fb.isFuzzyLeaveDate())
            {
                sqlDepart1hop = "select ap.AIRPORT_CODE as DEPART_AIRPORT, a.AIRPORT_CODE as ARRIVE_AIRPORT, fl.FLIGHT_PRICE as PriceTotal, fl.FLIGHT_PRICE as FIRST_FLIGHT_PRICE, fl.FLIGHT_ID as FIRST_FLIGHT_ID, ap.AIRPORT_CITY as DEPART_AIRPORT_CITY, ap.AIRPORT_STATE as DEPART_AIRPORT_STATE, " +
                    "a.AIRPORT_CITY as ARRIVE_AIRPORT_CITY, a.AIRPORT_STATE as ARRIVE_AIRPORT_STATE, " +
                    "al.AIRLINE_NAME, al.AIRLINE_ABBREVIATION, fl.FLIGHT_NUMBER, to_char(fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FLIGHT_DEPARTURE_TIME, " +
                    "to_char(fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FLIGHT_ARRIVAL_TIME from " +
                    "AIRPORT ap, AIRPORT a, AIRLINE al, FLIGHT fl where " +
                    "ap.AIRPORT_CITY='" + fb.getDepartCity() + "' and ap.AIRPORT_ID=fl.DEPART_FROM and " +
                    "al.AIRLINE_ID=fl.AIRLINE_ID and a.AIRPORT_CITY='" + fb.getArriveCity() + "' and " +
                    "a.AIRPORT_ID=fl.ARRIVE_AT and EXTRACT(YEAR FROM " +
                    "fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartYear() + " and EXTRACT(MONTH FROM " +
                    "fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartMonth() + " and EXTRACT(DAY FROM " +
                    "fl.FLIGHT_DEPARTURE_TIME)<=" + (fb.getDepartDay()+1) + " and EXTRACT(DAY FROM fl.FLIGHT_DEPARTURE_TIME)>=" + (fb.getDepartDay()-1) + " and EXTRACT(HOUR FROM " +
                    "fl.FLIGHT_DEPARTURE_TIME)<=" + fb.getDepartHourUpper() + " and EXTRACT(HOUR FROM " +
                    "fl.FLIGHT_DEPARTURE_TIME)>=" + fb.getDepartHourLower() +
                    " and EXTRACT(HOUR FROM fl.FLIGHT_ARRIVAL_TIME)<=" + fb.getDepartArrivalHourUpper() +
                    " and EXTRACT(HOUR FROM fl.FLIGHT_ARRIVAL_TIME)>=" + fb.getDepartArrivalHourLower();

                sqlDepart2hop = "select sour.AIRPORT_CODE as DEPART_AIRPORT, mid.AIRPORT_CODE as FIRST_MID_AIRPORT, dest.AIRPORT_CODE as ARRIVE_AIRPORT, (first_fl.FLIGHT_PRICE + second_fl.FLIGHT_PRICE) as PriceTotal, first_fl.FLIGHT_PRICE as FIRST_FLIGHT_PRICE, second_fl.FLIGHT_PRICE as SECOND_FLIGHT_PRICE, " +
                        "first_fl.FLIGHT_ID as FIRST_FLIGHT_ID, sour.AIRPORT_CITY as DEPART_AIRPORT_CITY, sour.AIRPORT_STATE as DEPART_AIRPORT_STATE, " +
                     "mid.AIRPORT_CITY as MID_AIRPORT_CITY, mid.AIRPORT_STATE as MID_AIRPORT_STATE, " +
                     "first_al.AIRLINE_NAME as FIRST_AIRLINE_NAME, first_al.AIRLINE_ABBREVIATION as FIRST_AIRLINE_ABBREVIATION, first_fl.FLIGHT_NUMBER as FIRST_FLIGHT_NUMBER, " +
                     "to_char(first_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_DEPARTURE_TIME, " +
                     "to_char(first_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_ARRIVAL_TIME, " +
                     "second_fl.FLIGHT_ID as SECOND_FLIGHT_ID, dest.AIRPORT_CITY as ARRIVE_AIRPORT_CITY, dest.AIRPORT_STATE as ARRIVE_AIRPORT_STATE, " +
                     "second_al.AIRLINE_NAME as SECOND_AIRLINE_NAME, second_al.AIRLINE_ABBREVIATION as SECOND_AIRLINE_ABBREVIATION, " +
                     "second_fl.FLIGHT_NUMBER as SECOND_FLIGHT_NUMBER, to_char(second_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_DEPARTURE_TIME, " +
                     "to_char(second_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_ARRIVAL_TIME " +
                     "from AIRPORT sour, AIRPORT mid, AIRPORT dest, AIRLINE first_al, AIRLINE second_al, FLIGHT first_fl, FLIGHT second_fl " +
                     "where sour.AIRPORT_CITY='" + fb.getDepartCity() + "' and dest.AIRPORT_CITY='" + fb.getArriveCity() + "' and sour.AIRPORT_ID=first_fl.DEPART_FROM and " +
                     "dest.AIRPORT_ID=second_fl.ARRIVE_AT and first_fl.ARRIVE_AT=second_fl.DEPART_FROM and first_fl.ARRIVE_AT=mid.AIRPORT_ID " +
                     "and first_al.AIRLINE_ID=first_fl.AIRLINE_ID and  second_al.AIRLINE_ID=second_fl.AIRLINE_ID and " +
                     "EXTRACT(YEAR FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartYear() + " and " +
                     "EXTRACT(MONTH FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartMonth() + " and " +
                     "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + (fb.getDepartDay()+1) + " and " +
                     "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + (fb.getDepartDay()-1) + " and " +
                     "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + fb.getDepartHourUpper() + " and " +
                     "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + fb.getDepartHourLower() + " and " +
                     "EXTRACT(HOUR FROM second_fl.FLIGHT_ARRIVAL_TIME)<=" + fb.getDepartArrivalHourUpper() + " and " +
                     "EXTRACT(HOUR FROM second_fl.FLIGHT_ARRIVAL_TIME)>=" + fb.getDepartArrivalHourLower() + " and " +
                     "second_fl.FLIGHT_DEPARTURE_TIME> first_fl.FLIGHT_ARRIVAL_TIME AND second_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day";

                sqlDepart3hop = "select sour.AIRPORT_CODE as DEPART_AIRPORT, first_mid.AIRPORT_CODE as FIRST_MID_AIRPORT, second_mid.AIRPORT_CODE as SECOND_MID_AIRPORT, dest.AIRPORT_CODE as ARRIVE_AIRPORT, (first_fl.FLIGHT_PRICE + second_fl.FLIGHT_PRICE + third_fl.FLIGHT_PRICE) as PriceTotal, first_fl.FLIGHT_PRICE as FIRST_FLIGHT_PRICE,second_fl.FLIGHT_PRICE as SECOND_FLIGHT_PRICE, third_fl.FLIGHT_PRICE as THIRD_FLIGHT_PRICE, " +
                        "first_fl.FLIGHT_ID as FIRST_FLIGHT_ID, second_fl.FLIGHT_ID as SECOND_FLIGHT_ID, third_fl.FLIGHT_ID as THIRD_FLIGHT_ID, sour.AIRPORT_CITY as DEPART_AIRPORT_CITY, sour.AIRPORT_STATE as DEPART_AIRPORT_STATE, " +
                      "first_al.AIRLINE_NAME as FIRST_AIRLINE_NAME, first_al.AIRLINE_ABBREVIATION as FIRST_AIRLINE_ABBREVIATION, first_fl.FLIGHT_NUMBER as FIRST_FLIGHT_NUMBER, " +
                      "to_char(first_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(first_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_ARRIVAL_TIME, " +
                      "first_mid.AIRPORT_CITY as FIRST_MID_AIRPORT_CITY, first_mid.AIRPORT_STATE as FIRST_MID_AIRPORT_STATE, " +
                      "second_mid.AIRPORT_CITY as SECOND_MID_AIRPORT_CITY, second_mid.AIRPORT_STATE as SECOND_MID_AIRPORT_STATE, " +
                      "second_al.AIRLINE_NAME as SECOND_AIRLINE_NAME, second_al.AIRLINE_ABBREVIATION as SECOND_AIRLINE_ABBREVIATION, second_fl.FLIGHT_NUMBER as SECOND_FLIGHT_NUMBER, " +
                      "to_char(second_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(second_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_ARRIVAL_TIME, " +
                      "dest.AIRPORT_CITY as ARRIVE_AIRPORT_CITY, dest.AIRPORT_STATE as ARRIVE_AIRPORT_STATE, third_al.AIRLINE_NAME as THIRD_AIRLINE_NAME, " +
                      "third_al.AIRLINE_ABBREVIATION as THIRD_AIRLINE_ABBREVIATION, third_fl.FLIGHT_NUMBER as THIRD_FLIGHT_NUMBER, " +
                      "to_char(third_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(third_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_ARRIVAL_TIME " +
                      "from AIRPORT sour, AIRPORT first_mid, AIRPORT second_mid, AIRPORT dest, AIRLINE first_al, AIRLINE second_al, AIRLINE third_al, FLIGHT first_fl, " +
                      "FLIGHT second_fl, FLIGHT third_fl " +
                      "where sour.AIRPORT_CITY='" + fb.getDepartCity() + "' and dest.AIRPORT_CITY='" + fb.getArriveCity() + "' and sour.AIRPORT_ID=first_fl.DEPART_FROM and " +
                      "dest.AIRPORT_ID=third_fl.ARRIVE_AT and first_fl.ARRIVE_AT=second_fl.DEPART_FROM and first_fl.ARRIVE_AT=first_mid.AIRPORT_ID and " +
                      "second_fl.ARRIVE_AT=third_fl.DEPART_FROM and second_fl.ARRIVE_AT=second_mid.AIRPORT_ID and first_al.AIRLINE_ID=first_fl.AIRLINE_ID and " +
                      "second_al.AIRLINE_ID=second_fl.AIRLINE_ID and third_al.AIRLINE_ID=third_fl.AIRLINE_ID and first_mid.AIRPORT_CITY<>dest.AIRPORT_CITY and sour.AIRPORT_CITY<>second_mid.AIRPORT_CITY and " +
                      "EXTRACT(YEAR FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartYear() + " and " +
                      "EXTRACT(MONTH FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartMonth() + " and " +
                      "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + (fb.getDepartDay()+1) + " and " +
                      "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + (fb.getDepartDay()-1) + " and " +
                      "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + fb.getDepartHourUpper() + " and " +
                      "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + fb.getDepartHourLower() + " and " +
                      "EXTRACT(HOUR FROM third_fl.FLIGHT_ARRIVAL_TIME)<=" + fb.getDepartArrivalHourUpper() + " and " +
                      "EXTRACT(HOUR FROM third_fl.FLIGHT_ARRIVAL_TIME)>=" + fb.getDepartArrivalHourLower() + " and " +
                      "second_fl.FLIGHT_DEPARTURE_TIME> first_fl.FLIGHT_ARRIVAL_TIME AND second_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day AND " +
                      "third_fl.FLIGHT_DEPARTURE_TIME> second_fl.FLIGHT_ARRIVAL_TIME AND third_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day";

                sqlDepart4hop = "select sour.AIRPORT_CODE as DEPART_AIRPORT, first_mid.AIRPORT_CODE as FIRST_MID_AIRPORT, second_mid.AIRPORT_CODE as SECOND_MID_AIRPORT, third_mid.AIRPORT_CODE as THIRD_MID_AIRPORT, dest.AIRPORT_CODE as ARRIVE_AIRPORT, (first_fl.FLIGHT_PRICE + second_fl.FLIGHT_PRICE + third_fl.FLIGHT_PRICE + fourth_fl.FLIGHT_PRICE) as PriceTotal, first_fl.FLIGHT_PRICE as FIRST_FLIGHT_PRICE, second_fl.FLIGHT_PRICE as SECOND_FLIGHT_PRICE, third_fl.FLIGHT_PRICE as THIRD_FLIGHT_PRICE, fourth_fl.FLIGHT_PRICE as FOURTH_FLIGHT_PRICE, " +
                        "first_fl.FLIGHT_ID as FIRST_FLIGHT_ID, second_fl.FLIGHT_ID as SECOND_FLIGHT_ID, third_fl.FLIGHT_ID as THIRD_FLIGHT_ID, fourth_fl.FLIGHT_ID as FOURTH_FLIGHT_ID, sour.AIRPORT_CITY as DEPART_AIRPORT_CITY, sour.AIRPORT_STATE as DEPART_AIRPORT_STATE, " +
                      "first_al.AIRLINE_NAME as FIRST_AIRLINE_NAME, first_al.AIRLINE_ABBREVIATION as FIRST_AIRLINE_ABBREVIATION, first_fl.FLIGHT_NUMBER as FIRST_FLIGHT_NUMBER, " +
                      "to_char(first_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(first_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_ARRIVAL_TIME, " +
                      "first_mid.AIRPORT_CITY as FIRST_MID_AIRPORT_CITY, first_mid.AIRPORT_STATE as FIRST_MID_AIRPORT_STATE, " +
                      "second_mid.AIRPORT_CITY as SECOND_MID_AIRPORT_CITY, second_mid.AIRPORT_STATE as SECOND_MID_AIRPORT_STATE, " +
                      "third_mid.AIRPORT_CITY as THIRD_MID_AIRPORT_CITY, third_mid.AIRPORT_STATE as THIRD_MID_AIRPORT_STATE, " +
                      "second_al.AIRLINE_NAME as SECOND_AIRLINE_NAME, second_al.AIRLINE_ABBREVIATION as SECOND_AIRLINE_ABBREVIATION, second_fl.FLIGHT_NUMBER as SECOND_FLIGHT_NUMBER, " +
                      "to_char(second_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(second_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_ARRIVAL_TIME, " +
                      "dest.AIRPORT_CITY as ARRIVE_AIRPORT_CITY, dest.AIRPORT_STATE as ARRIVE_AIRPORT_STATE, third_al.AIRLINE_NAME as THIRD_AIRLINE_NAME, " +
                      "third_al.AIRLINE_ABBREVIATION as THIRD_AIRLINE_ABBREVIATION, third_fl.FLIGHT_NUMBER as THIRD_FLIGHT_NUMBER, " +
                      "to_char(third_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(third_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_ARRIVAL_TIME, " +
                      "fourth_al.AIRLINE_NAME as FOURTH_AIRLINE_NAME, fourth_al.AIRLINE_ABBREVIATION as FOURTH_AIRLINE_ABBREVIATION, fourth_fl.FLIGHT_NUMBER as FOURTH_FLIGHT_NUMBER, " +
                      "to_char(fourth_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FOURTH_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(fourth_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FOURTH_FLIGHT_ARRIVAL_TIME " +
                      "from AIRPORT sour, AIRPORT first_mid, AIRPORT second_mid, AIRPORT third_mid, AIRPORT dest, AIRLINE first_al, AIRLINE second_al, AIRLINE third_al, AIRLINE fourth_al, FLIGHT first_fl, " +
                      "FLIGHT second_fl, FLIGHT third_fl, FLIGHT fourth_fl " +
                      "where sour.AIRPORT_CITY='" + fb.getDepartCity() + "' and dest.AIRPORT_CITY='" + fb.getArriveCity() + "' and sour.AIRPORT_ID=first_fl.DEPART_FROM and " +
                      "dest.AIRPORT_ID=fourth_fl.ARRIVE_AT and first_fl.ARRIVE_AT=second_fl.DEPART_FROM and first_fl.ARRIVE_AT=first_mid.AIRPORT_ID and " +
                      "second_fl.ARRIVE_AT=third_fl.DEPART_FROM and second_fl.ARRIVE_AT=second_mid.AIRPORT_ID and first_al.AIRLINE_ID=first_fl.AIRLINE_ID and " +
                      "second_al.AIRLINE_ID=second_fl.AIRLINE_ID and third_al.AIRLINE_ID=third_fl.AIRLINE_ID and fourth_al.AIRLINE_ID=fourth_fl.AIRLINE_ID and third_fl.ARRIVE_AT=fourth_fl.DEPART_FROM and third_fl.ARRIVE_AT=third_mid.AIRPORT_ID and " +
                      "first_mid.AIRPORT_CITY<>dest.AIRPORT_CITY and first_mid.AIRPORT_CITY<>third_mid.AIRPORT_CITY and sour.AIRPORT_CITY<>second_mid.AIRPORT_CITY and sour.AIRPORT_CITY<>third_mid.AIRPORT_CITY and dest.AIRPORT_CITY<>second_mid.AIRPORT_CITY and dest.AIRPORT_CITY<>third_mid.AIRPORT_CITY and " +
                      "EXTRACT(YEAR FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartYear() + " and " +
                      "EXTRACT(MONTH FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartMonth() + " and " +
                      "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + (fb.getDepartDay()+1) + " and " +
                      "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + (fb.getDepartDay()-1) + " and " +
                      "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + fb.getDepartHourUpper() + " and " +
                      "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + fb.getDepartHourLower() + " and " +
                      "EXTRACT(HOUR FROM fourth_fl.FLIGHT_ARRIVAL_TIME)<=" + fb.getDepartArrivalHourUpper() + " and " +
                      "EXTRACT(HOUR FROM fourth_fl.FLIGHT_ARRIVAL_TIME)>=" + fb.getDepartArrivalHourLower() + " and " +
                      "second_fl.FLIGHT_DEPARTURE_TIME> first_fl.FLIGHT_ARRIVAL_TIME AND second_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day AND " +
                      "third_fl.FLIGHT_DEPARTURE_TIME> second_fl.FLIGHT_ARRIVAL_TIME AND third_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day and " +
                      "fourth_fl.FLIGHT_DEPARTURE_TIME> third_fl.FLIGHT_ARRIVAL_TIME AND fourth_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day";

                sqlDepart5hop = "select sour.AIRPORT_CODE as DEPART_AIRPORT, first_mid.AIRPORT_CODE as FIRST_MID_AIRPORT, second_mid.AIRPORT_CODE as SECOND_MID_AIRPORT, third_mid.AIRPORT_CODE as THIRD_MID_AIRPORT, fourth_mid.AIRPORT_CODE as FOURTH_MID_AIRPORT, dest.AIRPORT_CODE as ARRIVE_AIRPORT, (first_fl.FLIGHT_PRICE + second_fl.FLIGHT_PRICE + third_fl.FLIGHT_PRICE + fourth_fl.FLIGHT_PRICE + fifth_fl.FLIGHT_PRICE) as PriceTotal ,first_fl.FLIGHT_ID as FIRST_FLIGHT_ID, second_fl.FLIGHT_ID as SECOND_FLIGHT_ID, third_fl.FLIGHT_ID as THIRD_FLIGHT_ID, fourth_fl.FLIGHT_ID as FOURTH_FLIGHT_ID, fifth_fl.FLIGHT_ID as FIFTH_FLIGHT_ID, sour.AIRPORT_CITY as DEPART_AIRPORT_CITY, sour.AIRPORT_STATE as DEPART_AIRPORT_STATE," +
                    "first_al.AIRLINE_NAME as FIRST_AIRLINE_NAME, first_al.AIRLINE_ABBREVIATION as FIRST_AIRLINE_ABBREVIATION, first_fl.FLIGHT_NUMBER as FIRST_FLIGHT_NUMBER," +
                    "to_char(first_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_DEPARTURE_TIME," +
                    "to_char(first_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_ARRIVAL_TIME," +
                    "first_mid.AIRPORT_CITY as FIRST_MID_AIRPORT_CITY, first_mid.AIRPORT_STATE as FIRST_MID_AIRPORT_STATE," +
                    "second_mid.AIRPORT_CITY as SECOND_MID_AIRPORT_CITY, second_mid.AIRPORT_STATE as SECOND_MID_AIRPORT_STATE," +
                    "third_mid.AIRPORT_CITY as THIRD_MID_AIRPORT_CITY, third_mid.AIRPORT_STATE as THIRD_MID_AIRPORT_STATE,"+
                    "fourth_mid.AIRPORT_CITY as FOURTH_MID_AIRPORT_CITY, fourth_mid.AIRPORT_STATE as FOURTH_MID_AIRPORT_STATE,"+
                    "second_al.AIRLINE_NAME as SECOND_AIRLINE_NAME, second_al.AIRLINE_ABBREVIATION as SECOND_AIRLINE_ABBREVIATION, second_fl.FLIGHT_NUMBER as SECOND_FLIGHT_NUMBER,"+
                    "to_char(second_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_DEPARTURE_TIME,"+
                    "to_char(second_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_ARRIVAL_TIME,"+
                    "dest.AIRPORT_CITY as ARRIVE_AIRPORT_CITY, dest.AIRPORT_STATE as ARRIVE_AIRPORT_STATE, third_al.AIRLINE_NAME as THIRD_AIRLINE_NAME,"+
                    "third_al.AIRLINE_ABBREVIATION as THIRD_AIRLINE_ABBREVIATION, third_fl.FLIGHT_NUMBER as THIRD_FLIGHT_NUMBER,"+
                    "to_char(third_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_DEPARTURE_TIME,"+
                    "to_char(third_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_ARRIVAL_TIME,"+
                    "fourth_al.AIRLINE_NAME as FOURTH_AIRLINE_NAME, fourth_al.AIRLINE_ABBREVIATION as FOURTH_AIRLINE_ABBREVIATION, fourth_fl.FLIGHT_NUMBER as FOURTH_FLIGHT_NUMBER,"+
                    "to_char(fourth_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FOURTH_FLIGHT_DEPARTURE_TIME,"+
                    "to_char(fourth_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FOURTH_FLIGHT_ARRIVAL_TIME,"+
                    "fifth_al.AIRLINE_NAME as FIFTH_AIRLINE_NAME, fifth_al.AIRLINE_ABBREVIATION as FIFTH_AIRLINE_ABBREVIATION, fifth_fl.FLIGHT_NUMBER as FIFTH_FLIGHT_NUMBER,"+
                    "to_char(fifth_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FIFTH_FLIGHT_DEPARTURE_TIME,"+
                    "to_char(fifth_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FIFTH_FLIGHT_ARRIVAL_TIME "+
                    "from AIRPORT sour, AIRPORT first_mid, AIRPORT second_mid, AIRPORT third_mid, AIRPORT fourth_mid, AIRPORT dest, AIRLINE first_al, AIRLINE second_al, AIRLINE third_al, AIRLINE fourth_al, AIRLINE fifth_al, FLIGHT first_fl,"+
                    "FLIGHT second_fl, FLIGHT third_fl, FLIGHT fourth_fl, FLIGHT fifth_fl "+
                    "where sour.AIRPORT_CITY='" + fb.getDepartCity() + "' and dest.AIRPORT_CITY='" + fb.getArriveCity() + "' and sour.AIRPORT_ID=first_fl.DEPART_FROM and "+
                    "dest.AIRPORT_ID=fifth_fl.ARRIVE_AT and first_fl.ARRIVE_AT=second_fl.DEPART_FROM and first_fl.ARRIVE_AT=first_mid.AIRPORT_ID and "+
                    "second_fl.ARRIVE_AT=third_fl.DEPART_FROM and second_fl.ARRIVE_AT=second_mid.AIRPORT_ID and first_al.AIRLINE_ID=first_fl.AIRLINE_ID and "+
                    "second_al.AIRLINE_ID=second_fl.AIRLINE_ID and third_al.AIRLINE_ID=third_fl.AIRLINE_ID and fourth_al.AIRLINE_ID=fourth_fl.AIRLINE_ID and fifth_al.AIRLINE_ID=fifth_fl.AIRLINE_ID and third_fl.ARRIVE_AT=fourth_fl.DEPART_FROM and third_fl.ARRIVE_AT=third_mid.AIRPORT_ID and fourth_fl.ARRIVE_AT=fifth_fl.DEPART_FROM and fourth_fl.ARRIVE_AT=fourth_mid.AIRPORT_ID and "+
                    "first_mid.AIRPORT_CITY<>dest.AIRPORT_CITY and first_mid.AIRPORT_CITY<>fourth_mid.AIRPORT_CITY and first_mid.AIRPORT_CITY<>third_mid.AIRPORT_CITY and sour.AIRPORT_CITY<>second_mid.AIRPORT_CITY and sour.AIRPORT_CITY<>third_mid.AIRPORT_CITY and sour.AIRPORT_CITY<>fourth_mid.AIRPORT_CITY and second_mid.AIRPORT_CITY<>fourth_mid.AIRPORT_CITY and dest.AIRPORT_CITY<>second_mid.AIRPORT_CITY and dest.AIRPORT_CITY<>third_mid.AIRPORT_CITY and "+
                    "EXTRACT(YEAR FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartYear() + " and " +
                    "EXTRACT(MONTH FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getDepartMonth() + " and " +
                    "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + (fb.getDepartDay()+1) + " and " +
                    "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + (fb.getDepartDay()-1) + " and " +
                    "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + fb.getDepartHourUpper() + " and " +
                    "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + fb.getDepartHourLower() + " and " +
                    "EXTRACT(HOUR FROM fourth_fl.FLIGHT_ARRIVAL_TIME)<=" + fb.getDepartArrivalHourUpper() + " and " +
                    "EXTRACT(HOUR FROM fourth_fl.FLIGHT_ARRIVAL_TIME)>=" + fb.getDepartArrivalHourLower() + " and " +
                    "second_fl.FLIGHT_DEPARTURE_TIME> first_fl.FLIGHT_ARRIVAL_TIME AND second_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day and "+
                    "third_fl.FLIGHT_DEPARTURE_TIME> second_fl.FLIGHT_ARRIVAL_TIME AND third_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day and "+
                    "fourth_fl.FLIGHT_DEPARTURE_TIME> third_fl.FLIGHT_ARRIVAL_TIME AND fourth_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day and "+
                    "fifth_fl.FLIGHT_DEPARTURE_TIME> fourth_fl.FLIGHT_ARRIVAL_TIME AND fifth_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day";

            }

            if (fb.isFuzzyReturnDate())
            {//add PriceTotal
                sqlReturn1hop = "select ap.AIRPORT_CODE as DEPART_AIRPORT, a.AIRPORT_CODE as ARRIVE_AIRPORT, fl.FLIGHT_PRICE as PriceTotal, fl.FLIGHT_PRICE as FIRST_FLIGHT_PRICE, fl.FLIGHT_ID as FIRST_FLIGHT_ID, ap.AIRPORT_CITY as DEPART_AIRPORT_CITY, ap.AIRPORT_STATE as DEPART_AIRPORT_STATE, " +
                    "a.AIRPORT_CITY as ARRIVE_AIRPORT_CITY, a.AIRPORT_STATE as ARRIVE_AIRPORT_STATE, " +
                    "al.AIRLINE_NAME, al.AIRLINE_ABBREVIATION, fl.FLIGHT_NUMBER, to_char(fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FLIGHT_DEPARTURE_TIME, " +
                    "to_char(fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FLIGHT_ARRIVAL_TIME from " +
                    "AIRPORT ap, AIRPORT a, AIRLINE al, FLIGHT fl where " +
                    "ap.AIRPORT_CITY='" + fb.getArriveCity() + "' and ap.AIRPORT_ID=fl.DEPART_FROM and " +
                    "al.AIRLINE_ID=fl.AIRLINE_ID and a.AIRPORT_CITY='" + fb.getDepartCity() + "' and " +
                    "a.AIRPORT_ID=fl.ARRIVE_AT and EXTRACT(YEAR FROM " +
                    "fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnYear() + " and EXTRACT(MONTH FROM " +
                    "fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnMonth() + " and EXTRACT(DAY FROM " +
                    "fl.FLIGHT_DEPARTURE_TIME)<=" + (fb.getReturnDay()+1) + " and EXTRACT(DAY FROM fl.FLIGHT_DEPARTURE_TIME)>=" + (fb.getReturnDay()-1) + " and EXTRACT(HOUR FROM " +
                    "fl.FLIGHT_DEPARTURE_TIME)<=" + fb.getReturnHourUpper() + " and EXTRACT(HOUR FROM " +
                    "fl.FLIGHT_DEPARTURE_TIME)>=" + fb.getReturnHourLower() +
                    " and EXTRACT(HOUR FROM fl.FLIGHT_ARRIVAL_TIME)<=" + fb.getReturnArrivalHourUpper() +
                    " and EXTRACT(HOUR FROM fl.FLIGHT_ARRIVAL_TIME)>=" + fb.getReturnArrivalHourLower();

                sqlReturn2hop = "select sour.AIRPORT_CODE as DEPART_AIRPORT, mid.AIRPORT_CODE as FIRST_MID_AIRPORT, dest.AIRPORT_CODE as ARRIVE_AIRPORT, (first_fl.FLIGHT_PRICE + second_fl.FLIGHT_PRICE ) as PriceTotal, first_fl.FLIGHT_PRICE as FIRST_FLIGHT_PRICE, second_fl.FLIGHT_PRICE as SECOND_FLIGHT_PRICE, "
                        + "first_fl.FLIGHT_ID as FIRST_FLIGHT_ID, sour.AIRPORT_CITY as DEPART_AIRPORT_CITY, sour.AIRPORT_STATE as DEPART_AIRPORT_STATE, " +
                     "mid.AIRPORT_CITY as MID_AIRPORT_CITY, mid.AIRPORT_STATE as MID_AIRPORT_STATE, " +
                     "first_al.AIRLINE_NAME as FIRST_AIRLINE_NAME, first_al.AIRLINE_ABBREVIATION as FIRST_AIRLINE_ABBREVIATION, first_fl.FLIGHT_NUMBER as FIRST_FLIGHT_NUMBER, " +
                     "to_char(first_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_DEPARTURE_TIME, " +
                     "to_char(first_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_ARRIVAL_TIME, " +
                     "second_fl.FLIGHT_ID as SECOND_FLIGHT_ID, dest.AIRPORT_CITY as ARRIVE_AIRPORT_CITY, dest.AIRPORT_STATE as ARRIVE_AIRPORT_STATE, " +
                     "second_al.AIRLINE_NAME as SECOND_AIRLINE_NAME, second_al.AIRLINE_ABBREVIATION as SECOND_AIRLINE_ABBREVIATION, " +
                     "second_fl.FLIGHT_NUMBER as SECOND_FLIGHT_NUMBER, to_char(second_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_DEPARTURE_TIME, " +
                     "to_char(second_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_ARRIVAL_TIME " +
                     "from AIRPORT sour, AIRPORT mid, AIRPORT dest, AIRLINE first_al, AIRLINE second_al, FLIGHT first_fl, FLIGHT second_fl " +
                     "where sour.AIRPORT_CITY='" + fb.getArriveCity() + "' and dest.AIRPORT_CITY='" + fb.getDepartCity() + "' and sour.AIRPORT_ID=first_fl.DEPART_FROM and " +
                     "dest.AIRPORT_ID=second_fl.ARRIVE_AT and first_fl.ARRIVE_AT=second_fl.DEPART_FROM and first_fl.ARRIVE_AT=mid.AIRPORT_ID " +
                     "and first_al.AIRLINE_ID=first_fl.AIRLINE_ID and  second_al.AIRLINE_ID=second_fl.AIRLINE_ID and " +
                     "EXTRACT(YEAR FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnYear() + " and " +
                     "EXTRACT(MONTH FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnMonth() + " and " +
                     "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + (fb.getReturnDay()+1) + " and " +
                     "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + (fb.getReturnDay()-1) + " and " +
                     "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + fb.getReturnHourUpper() + " and " +
                     "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + fb.getReturnHourLower() + " and " +
                     "EXTRACT(HOUR FROM second_fl.FLIGHT_ARRIVAL_TIME)<=" + fb.getReturnArrivalHourUpper() + " and " +
                     "EXTRACT(HOUR FROM second_fl.FLIGHT_ARRIVAL_TIME)>=" + fb.getReturnArrivalHourLower() + " and " +
                     "second_fl.FLIGHT_DEPARTURE_TIME> first_fl.FLIGHT_ARRIVAL_TIME AND second_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day";

                sqlReturn3hop = "select sour.AIRPORT_CODE as DEPART_AIRPORT, first_mid.AIRPORT_CODE as FIRST_MID_AIRPORT, second_mid.AIRPORT_CODE as SECOND_MID_AIRPORT, dest.AIRPORT_CODE as ARRIVE_AIRPORT, (first_fl.FLIGHT_PRICE + second_fl.FLIGHT_PRICE + third_fl.FLIGHT_PRICE) as PriceTotal, first_fl.FLIGHT_PRICE as FIRST_FLIGHT_PRICE,second_fl.FLIGHT_PRICE as SECOND_FLIGHT_PRICE, third_fl.FLIGHT_PRICE as THIRD_FLIGHT_PRICE, " +
                      "first_fl.FLIGHT_ID as FIRST_FLIGHT_ID, second_fl.FLIGHT_ID as SECOND_FLIGHT_ID, third_fl.FLIGHT_ID as THIRD_FLIGHT_ID, sour.AIRPORT_CITY as DEPART_AIRPORT_CITY, sour.AIRPORT_STATE as DEPART_AIRPORT_STATE, " +
                      "first_al.AIRLINE_NAME as FIRST_AIRLINE_NAME, first_al.AIRLINE_ABBREVIATION as FIRST_AIRLINE_ABBREVIATION, first_fl.FLIGHT_NUMBER as FIRST_FLIGHT_NUMBER, " +
                      "to_char(first_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(first_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_ARRIVAL_TIME, " +
                      "first_mid.AIRPORT_CITY as FIRST_MID_AIRPORT_CITY, first_mid.AIRPORT_STATE as FIRST_MID_AIRPORT_STATE, " +
                      "second_mid.AIRPORT_CITY as SECOND_MID_AIRPORT_CITY, second_mid.AIRPORT_STATE as SECOND_MID_AIRPORT_STATE, " +
                      "second_al.AIRLINE_NAME as SECOND_AIRLINE_NAME, second_al.AIRLINE_ABBREVIATION as SECOND_AIRLINE_ABBREVIATION, second_fl.FLIGHT_NUMBER as SECOND_FLIGHT_NUMBER, " +
                      "to_char(second_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(second_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_ARRIVAL_TIME, " +
                      "dest.AIRPORT_CITY as ARRIVE_AIRPORT_CITY, dest.AIRPORT_STATE as ARRIVE_AIRPORT_STATE, third_al.AIRLINE_NAME as THIRD_AIRLINE_NAME, " +
                      "third_al.AIRLINE_ABBREVIATION as THIRD_AIRLINE_ABBREVIATION, third_fl.FLIGHT_NUMBER as THIRD_FLIGHT_NUMBER, " +
                      "to_char(third_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(third_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_ARRIVAL_TIME " +
                      "from AIRPORT sour, AIRPORT first_mid, AIRPORT second_mid, AIRPORT dest, AIRLINE first_al, AIRLINE second_al, AIRLINE third_al, FLIGHT first_fl, " +
                      "FLIGHT second_fl, FLIGHT third_fl " +
                      "where sour.AIRPORT_CITY='" + fb.getArriveCity() + "' and dest.AIRPORT_CITY='" + fb.getDepartCity() + "' and sour.AIRPORT_ID=first_fl.DEPART_FROM and " +
                      "dest.AIRPORT_ID=third_fl.ARRIVE_AT and first_fl.ARRIVE_AT=second_fl.DEPART_FROM and first_fl.ARRIVE_AT=first_mid.AIRPORT_ID and " +
                      "second_fl.ARRIVE_AT=third_fl.DEPART_FROM and second_fl.ARRIVE_AT=second_mid.AIRPORT_ID and first_al.AIRLINE_ID=first_fl.AIRLINE_ID and " +
                      "second_al.AIRLINE_ID=second_fl.AIRLINE_ID and third_al.AIRLINE_ID=third_fl.AIRLINE_ID and first_mid.AIRPORT_CITY<>dest.AIRPORT_CITY and sour.AIRPORT_CITY<>second_mid.AIRPORT_CITY and " +
                      "EXTRACT(YEAR FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnYear() + " and " +
                      "EXTRACT(MONTH FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnMonth() + " and " +
                      "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + (fb.getReturnDay()+1) + " and " +
                      "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + (fb.getReturnDay()-1) + " and " +
                      "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + fb.getReturnHourUpper() + " and " +
                      "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + fb.getReturnHourLower() + " and " +
                      "EXTRACT(HOUR FROM third_fl.FLIGHT_ARRIVAL_TIME)<=" + fb.getReturnArrivalHourUpper() + " and " +
                      "EXTRACT(HOUR FROM third_fl.FLIGHT_ARRIVAL_TIME)>=" + fb.getReturnArrivalHourLower() + " and " +
                      "second_fl.FLIGHT_DEPARTURE_TIME> first_fl.FLIGHT_ARRIVAL_TIME AND second_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day AND " +
                      "third_fl.FLIGHT_DEPARTURE_TIME> second_fl.FLIGHT_ARRIVAL_TIME AND third_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day";

                sqlReturn4hop = "select sour.AIRPORT_CODE as DEPART_AIRPORT, first_mid.AIRPORT_CODE as FIRST_MID_AIRPORT, second_mid.AIRPORT_CODE as SECOND_MID_AIRPORT, third_mid.AIRPORT_CODE as THIRD_MID_AIRPORT, dest.AIRPORT_CODE as ARRIVE_AIRPORT, (first_fl.FLIGHT_PRICE + second_fl.FLIGHT_PRICE + third_fl.FLIGHT_PRICE + fourth_fl.FLIGHT_PRICE) as PriceTotal, first_fl.FLIGHT_PRICE as FIRST_FLIGHT_PRICE, second_fl.FLIGHT_PRICE as SECOND_FLIGHT_PRICE, third_fl.FLIGHT_PRICE as THIRD_FLIGHT_PRICE, fourth_fl.FLIGHT_PRICE as FOURTH_FLIGHT_PRICE, " +
                        "first_fl.FLIGHT_ID as FIRST_FLIGHT_ID, second_fl.FLIGHT_ID as SECOND_FLIGHT_ID, third_fl.FLIGHT_ID as THIRD_FLIGHT_ID, fourth_fl.FLIGHT_ID as FOURTH_FLIGHT_ID, sour.AIRPORT_CITY as DEPART_AIRPORT_CITY, sour.AIRPORT_STATE as DEPART_AIRPORT_STATE, " +
                      "first_al.AIRLINE_NAME as FIRST_AIRLINE_NAME, first_al.AIRLINE_ABBREVIATION as FIRST_AIRLINE_ABBREVIATION, first_fl.FLIGHT_NUMBER as FIRST_FLIGHT_NUMBER, " +
                      "to_char(first_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(first_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_ARRIVAL_TIME, " +
                      "first_mid.AIRPORT_CITY as FIRST_MID_AIRPORT_CITY, first_mid.AIRPORT_STATE as FIRST_MID_AIRPORT_STATE, " +
                      "second_mid.AIRPORT_CITY as SECOND_MID_AIRPORT_CITY, second_mid.AIRPORT_STATE as SECOND_MID_AIRPORT_STATE, " +
                      "third_mid.AIRPORT_CITY as THIRD_MID_AIRPORT_CITY, third_mid.AIRPORT_STATE as THIRD_MID_AIRPORT_STATE, " +
                      "second_al.AIRLINE_NAME as SECOND_AIRLINE_NAME, second_al.AIRLINE_ABBREVIATION as SECOND_AIRLINE_ABBREVIATION, second_fl.FLIGHT_NUMBER as SECOND_FLIGHT_NUMBER, " +
                      "to_char(second_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(second_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_ARRIVAL_TIME, " +
                      "dest.AIRPORT_CITY as ARRIVE_AIRPORT_CITY, dest.AIRPORT_STATE as ARRIVE_AIRPORT_STATE, third_al.AIRLINE_NAME as THIRD_AIRLINE_NAME, " +
                      "third_al.AIRLINE_ABBREVIATION as THIRD_AIRLINE_ABBREVIATION, third_fl.FLIGHT_NUMBER as THIRD_FLIGHT_NUMBER, " +
                      "to_char(third_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(third_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_ARRIVAL_TIME, " +
                      "fourth_al.AIRLINE_NAME as FOURTH_AIRLINE_NAME, fourth_al.AIRLINE_ABBREVIATION as FOURTH_AIRLINE_ABBREVIATION, fourth_fl.FLIGHT_NUMBER as FOURTH_FLIGHT_NUMBER, " +
                      "to_char(fourth_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FOURTH_FLIGHT_DEPARTURE_TIME, " +
                      "to_char(fourth_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FOURTH_FLIGHT_ARRIVAL_TIME " +
                      "from AIRPORT sour, AIRPORT first_mid, AIRPORT second_mid, AIRPORT third_mid, AIRPORT dest, AIRLINE first_al, AIRLINE second_al, AIRLINE third_al, AIRLINE fourth_al, FLIGHT first_fl, " +
                      "FLIGHT second_fl, FLIGHT third_fl, FLIGHT fourth_fl " +
                      "where sour.AIRPORT_CITY='" + fb.getArriveCity() + "' and dest.AIRPORT_CITY='" + fb.getDepartCity() + "' and sour.AIRPORT_ID=first_fl.DEPART_FROM and " +
                      "dest.AIRPORT_ID=fourth_fl.ARRIVE_AT and first_fl.ARRIVE_AT=second_fl.DEPART_FROM and first_fl.ARRIVE_AT=first_mid.AIRPORT_ID and " +
                      "second_fl.ARRIVE_AT=third_fl.DEPART_FROM and second_fl.ARRIVE_AT=second_mid.AIRPORT_ID and first_al.AIRLINE_ID=first_fl.AIRLINE_ID and " +
                      "second_al.AIRLINE_ID=second_fl.AIRLINE_ID and third_al.AIRLINE_ID=third_fl.AIRLINE_ID and fourth_al.AIRLINE_ID=fourth_fl.AIRLINE_ID and third_fl.ARRIVE_AT=fourth_fl.DEPART_FROM and third_fl.ARRIVE_AT=third_mid.AIRPORT_ID and " +
                      "first_mid.AIRPORT_CITY<>dest.AIRPORT_CITY and first_mid.AIRPORT_CITY<>third_mid.AIRPORT_CITY and sour.AIRPORT_CITY<>second_mid.AIRPORT_CITY and sour.AIRPORT_CITY<>third_mid.AIRPORT_CITY and dest.AIRPORT_CITY<>second_mid.AIRPORT_CITY and dest.AIRPORT_CITY<>third_mid.AIRPORT_CITY and " +
                      "EXTRACT(YEAR FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnYear() + " and " +
                      "EXTRACT(MONTH FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnMonth() + " and " +
                      "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + (fb.getReturnDay()+1) + " and " +
                      "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + (fb.getReturnDay()-1) + " and " +
                      "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + fb.getReturnHourUpper() + " and " +
                      "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + fb.getReturnHourLower() + " and " +
                      "EXTRACT(HOUR FROM fourth_fl.FLIGHT_ARRIVAL_TIME)<=" + fb.getReturnArrivalHourUpper() + " and " +
                      "EXTRACT(HOUR FROM fourth_fl.FLIGHT_ARRIVAL_TIME)>=" + fb.getReturnArrivalHourLower() + " and " +
                      "second_fl.FLIGHT_DEPARTURE_TIME> first_fl.FLIGHT_ARRIVAL_TIME AND second_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day AND " +
                      "third_fl.FLIGHT_DEPARTURE_TIME> second_fl.FLIGHT_ARRIVAL_TIME AND third_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day and " +
                      "fourth_fl.FLIGHT_DEPARTURE_TIME> third_fl.FLIGHT_ARRIVAL_TIME AND fourth_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day";

                sqlReturn5hop = "select sour.AIRPORT_CODE as DEPART_AIRPORT, first_mid.AIRPORT_CODE as FIRST_MID_AIRPORT, second_mid.AIRPORT_CODE as SECOND_MID_AIRPORT, third_mid.AIRPORT_CODE as THIRD_MID_AIRPORT, fourth_mid.AIRPORT_CODE as FOURTH_MID_AIRPORT, dest.AIRPORT_CODE as ARRIVE_AIRPORT, (first_fl.FLIGHT_PRICE + second_fl.FLIGHT_PRICE + third_fl.FLIGHT_PRICE + fourth_fl.FLIGHT_PRICE + fifth_fl.FLIGHT_PRICE) as PriceTotal ,first_fl.FLIGHT_ID as FIRST_FLIGHT_ID, second_fl.FLIGHT_ID as SECOND_FLIGHT_ID, third_fl.FLIGHT_ID as THIRD_FLIGHT_ID, fourth_fl.FLIGHT_ID as FOURTH_FLIGHT_ID, fifth_fl.FLIGHT_ID as FIFTH_FLIGHT_ID, sour.AIRPORT_CITY as DEPART_AIRPORT_CITY, sour.AIRPORT_STATE as DEPART_AIRPORT_STATE," +
                    "first_al.AIRLINE_NAME as FIRST_AIRLINE_NAME, first_al.AIRLINE_ABBREVIATION as FIRST_AIRLINE_ABBREVIATION, first_fl.FLIGHT_NUMBER as FIRST_FLIGHT_NUMBER," +
                    "to_char(first_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_DEPARTURE_TIME," +
                    "to_char(first_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FIRST_FLIGHT_ARRIVAL_TIME," +
                    "first_mid.AIRPORT_CITY as FIRST_MID_AIRPORT_CITY, first_mid.AIRPORT_STATE as FIRST_MID_AIRPORT_STATE," +
                    "second_mid.AIRPORT_CITY as SECOND_MID_AIRPORT_CITY, second_mid.AIRPORT_STATE as SECOND_MID_AIRPORT_STATE," +
                    "third_mid.AIRPORT_CITY as THIRD_MID_AIRPORT_CITY, third_mid.AIRPORT_STATE as THIRD_MID_AIRPORT_STATE,"+
                    "fourth_mid.AIRPORT_CITY as FOURTH_MID_AIRPORT_CITY, fourth_mid.AIRPORT_STATE as FOURTH_MID_AIRPORT_STATE,"+
                    "second_al.AIRLINE_NAME as SECOND_AIRLINE_NAME, second_al.AIRLINE_ABBREVIATION as SECOND_AIRLINE_ABBREVIATION, second_fl.FLIGHT_NUMBER as SECOND_FLIGHT_NUMBER,"+
                    "to_char(second_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_DEPARTURE_TIME,"+
                    "to_char(second_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as SECOND_FLIGHT_ARRIVAL_TIME,"+
                    "dest.AIRPORT_CITY as ARRIVE_AIRPORT_CITY, dest.AIRPORT_STATE as ARRIVE_AIRPORT_STATE, third_al.AIRLINE_NAME as THIRD_AIRLINE_NAME,"+
                    "third_al.AIRLINE_ABBREVIATION as THIRD_AIRLINE_ABBREVIATION, third_fl.FLIGHT_NUMBER as THIRD_FLIGHT_NUMBER,"+
                    "to_char(third_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_DEPARTURE_TIME,"+
                    "to_char(third_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as THIRD_FLIGHT_ARRIVAL_TIME,"+
                    "fourth_al.AIRLINE_NAME as FOURTH_AIRLINE_NAME, fourth_al.AIRLINE_ABBREVIATION as FOURTH_AIRLINE_ABBREVIATION, fourth_fl.FLIGHT_NUMBER as FOURTH_FLIGHT_NUMBER,"+
                    "to_char(fourth_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FOURTH_FLIGHT_DEPARTURE_TIME,"+
                    "to_char(fourth_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FOURTH_FLIGHT_ARRIVAL_TIME,"+
                    "fifth_al.AIRLINE_NAME as FIFTH_AIRLINE_NAME, fifth_al.AIRLINE_ABBREVIATION as FIFTH_AIRLINE_ABBREVIATION, fifth_fl.FLIGHT_NUMBER as FIFTH_FLIGHT_NUMBER,"+
                    "to_char(fifth_fl.FLIGHT_DEPARTURE_TIME,'MM-DD-YYYY HH24:MI:SS') as FIFTH_FLIGHT_DEPARTURE_TIME,"+
                    "to_char(fifth_fl.FLIGHT_ARRIVAL_TIME,'MM-DD-YYYY HH24:MI:SS') as FIFTH_FLIGHT_ARRIVAL_TIME "+
                    "from AIRPORT sour, AIRPORT first_mid, AIRPORT second_mid, AIRPORT third_mid, AIRPORT fourth_mid, AIRPORT dest, AIRLINE first_al, AIRLINE second_al, AIRLINE third_al, AIRLINE fourth_al, AIRLINE fifth_al, FLIGHT first_fl,"+
                    "FLIGHT second_fl, FLIGHT third_fl, FLIGHT fourth_fl, FLIGHT fifth_fl "+
                    "where sour.AIRPORT_CITY='" + fb.getArriveCity() + "' and dest.AIRPORT_CITY='" + fb.getDepartCity() + "' and sour.AIRPORT_ID=first_fl.DEPART_FROM and "+
                    "dest.AIRPORT_ID=fifth_fl.ARRIVE_AT and first_fl.ARRIVE_AT=second_fl.DEPART_FROM and first_fl.ARRIVE_AT=first_mid.AIRPORT_ID and "+
                    "second_fl.ARRIVE_AT=third_fl.DEPART_FROM and second_fl.ARRIVE_AT=second_mid.AIRPORT_ID and first_al.AIRLINE_ID=first_fl.AIRLINE_ID and "+
                    "second_al.AIRLINE_ID=second_fl.AIRLINE_ID and third_al.AIRLINE_ID=third_fl.AIRLINE_ID and fourth_al.AIRLINE_ID=fourth_fl.AIRLINE_ID and fifth_al.AIRLINE_ID=fifth_fl.AIRLINE_ID and third_fl.ARRIVE_AT=fourth_fl.DEPART_FROM and third_fl.ARRIVE_AT=third_mid.AIRPORT_ID and fourth_fl.ARRIVE_AT=fifth_fl.DEPART_FROM and fourth_fl.ARRIVE_AT=fourth_mid.AIRPORT_ID and "+
                    "first_mid.AIRPORT_CITY<>dest.AIRPORT_CITY and first_mid.AIRPORT_CITY<>fourth_mid.AIRPORT_CITY and first_mid.AIRPORT_CITY<>third_mid.AIRPORT_CITY and sour.AIRPORT_CITY<>second_mid.AIRPORT_CITY and sour.AIRPORT_CITY<>third_mid.AIRPORT_CITY and sour.AIRPORT_CITY<>fourth_mid.AIRPORT_CITY and second_mid.AIRPORT_CITY<>fourth_mid.AIRPORT_CITY and dest.AIRPORT_CITY<>second_mid.AIRPORT_CITY and dest.AIRPORT_CITY<>third_mid.AIRPORT_CITY and "+
                    "EXTRACT(YEAR FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnYear() + " and " +
                    "EXTRACT(MONTH FROM first_fl.FLIGHT_DEPARTURE_TIME)=" + fb.getReturnMonth() + " and " +
                    "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + (fb.getReturnDay()+1) + " and " +
                    "EXTRACT(DAY FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + (fb.getReturnDay()-1) + " and " +
                    "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)<=" + fb.getReturnHourUpper() + " and " +
                    "EXTRACT(HOUR FROM first_fl.FLIGHT_DEPARTURE_TIME)>=" + fb.getReturnHourLower() + " and " +
                    "EXTRACT(HOUR FROM fourth_fl.FLIGHT_ARRIVAL_TIME)<=" + fb.getReturnArrivalHourUpper() + " and " +
                    "EXTRACT(HOUR FROM fourth_fl.FLIGHT_ARRIVAL_TIME)>=" + fb.getReturnArrivalHourLower() + " and " +
                    "second_fl.FLIGHT_DEPARTURE_TIME> first_fl.FLIGHT_ARRIVAL_TIME AND second_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day and "+
                    "third_fl.FLIGHT_DEPARTURE_TIME> second_fl.FLIGHT_ARRIVAL_TIME AND third_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day and "+
                    "fourth_fl.FLIGHT_DEPARTURE_TIME> third_fl.FLIGHT_ARRIVAL_TIME AND fourth_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day and "+
                    "fifth_fl.FLIGHT_DEPARTURE_TIME> fourth_fl.FLIGHT_ARRIVAL_TIME AND fifth_fl.FLIGHT_DEPARTURE_TIME< first_fl.FLIGHT_ARRIVAL_TIME + interval '2' day";

            }

            if ( fb.isSpecifiedAirline() == true ) {
                sqlDepart1hop = sqlDepart1hop + " and al.AIRLINE_NAME = '" + fb.getAirline() + "'";
                sqlDepart2hop = sqlDepart2hop + " and first_al.AIRLINE_NAME = '" + fb.getAirline() + "' and second_al.AIRLINE_NAME = '" + fb.getAirline() + "'";
                sqlDepart3hop = sqlDepart3hop + " and first_al.AIRLINE_NAME = '" + fb.getAirline() + "' and second_al.AIRLINE_NAME = '" + fb.getAirline() + "' and third_al.AIRLINE_NAME = '" + fb.getAirline() + "'";
                sqlDepart4hop = sqlDepart4hop + " and first_al.AIRLINE_NAME = '" + fb.getAirline() + "' and second_al.AIRLINE_NAME = '" + fb.getAirline() + "' and third_al.AIRLINE_NAME = '" + fb.getAirline() + "' and fourth_al.AIRLINE_NAME = '" + fb.getAirline() + "'";
                sqlDepart5hop = sqlDepart5hop + " and first_al.AIRLINE_NAME = '" + fb.getAirline() + "' and second_al.AIRLINE_NAME = '" + fb.getAirline() + "' and third_al.AIRLINE_NAME = '" + fb.getAirline() + "' and fourth_al.AIRLINE_NAME = '" + fb.getAirline() + "' and fifth_al.AIRLINE_NAME = '" + fb.getAirline() + "'";

                sqlReturn1hop = sqlReturn1hop + " and al.AIRLINE_NAME = '" + fb.getAirline() + "'";
                sqlReturn2hop = sqlReturn2hop + " and first_al.AIRLINE_NAME = '" + fb.getAirline() + "' and second_al.AIRLINE_NAME = '" + fb.getAirline() + "'";
                sqlReturn3hop = sqlReturn3hop + " and first_al.AIRLINE_NAME = '" + fb.getAirline() + "' and second_al.AIRLINE_NAME = '" + fb.getAirline() + "' and third_al.AIRLINE_NAME = '" + fb.getAirline() + "'";
                sqlReturn4hop = sqlReturn4hop + " and first_al.AIRLINE_NAME = '" + fb.getAirline() + "' and second_al.AIRLINE_NAME = '" + fb.getAirline() + "' and third_al.AIRLINE_NAME = '" + fb.getAirline() + "' and fourth_al.AIRLINE_NAME = '" + fb.getAirline() + "'";
                sqlReturn5hop = sqlReturn5hop + " and first_al.AIRLINE_NAME = '" + fb.getAirline() + "' and second_al.AIRLINE_NAME = '" + fb.getAirline() + "' and third_al.AIRLINE_NAME = '" + fb.getAirline() + "' and fourth_al.AIRLINE_NAME = '" + fb.getAirline() + "' and fifth_al.AIRLINE_NAME = '" + fb.getAirline() + "'";
            }

            if ( fb.getSeatingClass().equals("First")){
                //first class seat

                sqlDepart1hop = sqlDepart1hop + " and fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " order by PriceTotal asc";
                sqlDepart2hop = sqlDepart2hop + " and first_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and second_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " order by PriceTotal asc";
                sqlDepart3hop = sqlDepart3hop + " and first_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and second_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and third_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " order by PriceTotal asc";
                sqlDepart4hop = sqlDepart4hop + " and first_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and second_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and third_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and fourth_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " order by PriceTotal asc";
                sqlDepart5hop = sqlDepart5hop + " and first_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and second_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and third_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and fourth_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and fifth_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " order by PriceTotal asc";

                sqlReturn1hop = sqlReturn1hop + " and fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " order by PriceTotal asc";
                sqlReturn2hop = sqlReturn2hop + " and first_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and second_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " order by PriceTotal asc";
                sqlReturn3hop = sqlReturn3hop + " and first_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and second_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and third_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " order by PriceTotal asc";
                sqlReturn4hop = sqlReturn4hop + " and first_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and second_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and third_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and fourth_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " order by PriceTotal asc";
                sqlReturn5hop = sqlReturn5hop + " and first_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and second_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and third_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and fourth_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and fifth_fl.FLIGHT_FIRST_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " order by PriceTotal asc";
            }else {
                //coach class seat
                sqlDepart1hop = sqlDepart1hop + " and fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " order by PriceTotal asc";
                sqlDepart2hop = sqlDepart2hop + " and first_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and second_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " order by PriceTotal asc";
                sqlDepart3hop = sqlDepart3hop + " and first_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and second_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and third_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " order by PriceTotal asc";
                sqlDepart4hop = sqlDepart4hop + " and first_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and second_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and third_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and fourth_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " order by PriceTotal asc";
                sqlDepart5hop = sqlDepart5hop + " and first_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and second_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and third_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and fourth_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and fifth_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " order by PriceTotal asc";

                sqlReturn1hop = sqlReturn1hop + " and fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " order by PriceTotal asc";
                sqlReturn2hop = sqlReturn2hop + " and first_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and second_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " order by PriceTotal asc";
                sqlReturn3hop = sqlReturn3hop + " and first_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and second_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and third_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " order by PriceTotal asc";
                sqlReturn4hop = sqlReturn4hop + " and first_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and second_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and third_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and fourth_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " order by PriceTotal asc";
                sqlReturn5hop = sqlReturn5hop + " and first_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and second_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and third_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and fourth_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " and fifth_fl.FLIGHT_COACH_SEAT_REMAINING >= "+ fb.getNoOfTickets() + " order by PriceTotal asc";
            }



            //String strSql = "select * from (select AIRLINE.*,rownum as rno from AIRLINE) where rno<=" + pageNum * pageBean.rowsPerPage;
            PageBean pageBean = new PageBean(this);
            int pageNum = Integer.parseInt(page);
            Statement stmt = con.createStatement();
            Statement distanceStmt = con.createStatement();
            float limitDistanceFactor = (float) 1.5;

            // Add departure flight(s) (leave to trip) into v in PageBean
            // leave to trip: non-stop flight
            ResultSet rset = stmt.executeQuery(sqlDepart1hop);
            int i=0;
            int j=0;
            
            while(rset.next())
            {
                if(i > (pageNum - 1) * pageBean.rowsPerPage - 1 && i <= pageNum * pageBean.rowsPerPage - 1)
                {
                    String detailInfo = "";
                    Object[] obj = new Object[11];
                    obj[0] = new String("1");
                    obj[1] = new String(rset.getString("DEPART_AIRPORT_CITY") + "(" + rset.getString("DEPART_AIRPORT") + ")");
                    obj[2] = new String(rset.getString("ARRIVE_AIRPORT_CITY") + "(" + rset.getString("ARRIVE_AIRPORT") + ")");
                    obj[3] = new String(rset.getString("AIRLINE_NAME"));
                    obj[4] = new String(rset.getString("FLIGHT_NUMBER"));
                    obj[5] = new String(rset.getString("FLIGHT_DEPARTURE_TIME"));
                    obj[6] = new String(rset.getString("FLIGHT_ARRIVAL_TIME"));
                    String departState = rset.getString("DEPART_AIRPORT_STATE");
                    String arriveState = rset.getString("ARRIVE_AIRPORT_STATE");
                    detailInfo  = "Depart from: " + rset.getString("DEPART_AIRPORT_CITY") + "," + departState + " (" + rset.getString("DEPART_AIRPORT") + ") <br>";
                    detailInfo += "Arrive at:   " + rset.getString("ARRIVE_AIRPORT_CITY") + "," + arriveState + " (" + rset.getString("ARRIVE_AIRPORT") + ") <br>";
                    detailInfo += "Operated by: " + rset.getString("AIRLINE_NAME") + "<br>";
                    detailInfo += "Flight No.:  " + rset.getString("AIRLINE_ABBREVIATION") + rset.getString("FLIGHT_NUMBER") + "<br>";
                    detailInfo += "Departing...." + rset.getString("FLIGHT_DEPARTURE_TIME") + "<br>";
                    detailInfo += "Arriving....." + rset.getString("FLIGHT_ARRIVAL_TIME") + "<br>";
                    obj[7] = detailInfo;
                    obj[8] = new String(rset.getString("FIRST_FLIGHT_ID"));

                    // calculate distance between each hop, avoiding zigzag flights
                    String sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + departState + "' and upper(TO_STATE)='" + arriveState + "'";
                    ResultSet distanceRset = distanceStmt.executeQuery(sqlDistances);
                    distanceRset.next();
                    int originalDistance = distanceRset.getInt("DISTANCE");

                    obj[9] = new Integer(originalDistance/1609);                    
                    obj[10] = new Integer(rset.getInt("PriceTotal"));

                    v.add(obj);
                }
                i++;
            }

            // do not have to consider multiple-hop trips
            if (!fb.isNonStop())
            {

            // leave to trip: one-stop flight
            rset = stmt.executeQuery(sqlDepart2hop);
            while(rset.next())
            {
                String detailInfo = "";
                String flightnum = "";
                Object[] obj = new Object[11];
                obj[0] = new String("2");
                obj[1] = new String(rset.getString("DEPART_AIRPORT_CITY") + "(" + rset.getString("DEPART_AIRPORT") + ")");
                obj[2] = new String(rset.getString("ARRIVE_AIRPORT_CITY") + "(" + rset.getString("ARRIVE_AIRPORT") + ")");
                obj[3] = new String(rset.getString("FIRST_AIRLINE_NAME") + "/" + rset.getString("SECOND_AIRLINE_NAME"));
                obj[4] = new String(rset.getString("FIRST_FLIGHT_NUMBER") + "/" + rset.getString("SECOND_FLIGHT_NUMBER"));
                obj[5] = new String(rset.getString("FIRST_FLIGHT_DEPARTURE_TIME"));
                obj[6] = new String(rset.getString("SECOND_FLIGHT_ARRIVAL_TIME"));
                String departState = rset.getString("DEPART_AIRPORT_STATE");
                String arriveState = rset.getString("ARRIVE_AIRPORT_STATE");
                String midState = rset.getString("MID_AIRPORT_STATE");
                detailInfo  = "Depart from: " + rset.getString("DEPART_AIRPORT_CITY") + "," + departState + " (" + rset.getString("DEPART_AIRPORT") + ") <br>";
                detailInfo += "Arrive at:   " + rset.getString("ARRIVE_AIRPORT_CITY") + "," + arriveState + " (" + rset.getString("ARRIVE_AIRPORT") + ") <br>";
                detailInfo += "Connects in: " + rset.getString("MID_AIRPORT_CITY") + "," + midState + " (" + rset.getString("FIRST_MID_AIRPORT") + ") <br>";
                detailInfo += "Operated by: " + rset.getString("FIRST_AIRLINE_NAME") + "/" + rset.getString("SECOND_AIRLINE_NAME") + "<br><br>";
                detailInfo += "Flight 1 from " + rset.getString("DEPART_AIRPORT_CITY") + " to " + rset.getString("MID_AIRPORT_CITY") + "<br>";
                detailInfo += "Flight No.:  " + rset.getString("FIRST_AIRLINE_ABBREVIATION") + rset.getString("FIRST_FLIGHT_NUMBER") + "<br>";
                detailInfo += "Departing...." + rset.getString("FIRST_FLIGHT_DEPARTURE_TIME") + "<br>";
                detailInfo += "Arriving....." + rset.getString("FIRST_FLIGHT_ARRIVAL_TIME") + "<br><br>";
                detailInfo += "Flight 2 from " + rset.getString("MID_AIRPORT_CITY") + " to " + rset.getString("ARRIVE_AIRPORT_CITY") + "<br>";
                detailInfo += "Flight No.:  " + rset.getString("SECOND_AIRLINE_ABBREVIATION") + rset.getString("SECOND_FLIGHT_NUMBER") + "<br>";
                detailInfo += "Departing...." + rset.getString("SECOND_FLIGHT_DEPARTURE_TIME") + "<br>";
                detailInfo += "Arriving....." + rset.getString("SECOND_FLIGHT_ARRIVAL_TIME") + "<br>";
                obj[7] = detailInfo;
                flightnum = rset.getString("FIRST_FLIGHT_ID") + "." + rset.getString("SECOND_FLIGHT_ID");
                obj[8] = flightnum;

                // calculate distance between each hop, avoiding zigzag flights
                String sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + departState + "' and upper(TO_STATE)='" + arriveState + "'";
                ResultSet distanceRset = distanceStmt.executeQuery(sqlDistances);
                distanceRset.next();
                int originalDistance = distanceRset.getInt("DISTANCE");

                sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + departState + "' and upper(TO_STATE)='" + midState + "'";
                distanceRset = distanceStmt.executeQuery(sqlDistances);
                distanceRset.next();
                int hop1Distance = distanceRset.getInt("DISTANCE");

                sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + midState + "' and upper(TO_STATE)='" + arriveState + "'";
                distanceRset = distanceStmt.executeQuery(sqlDistances);
                distanceRset.next();
                int hop2Distance = distanceRset.getInt("DISTANCE");
                
                obj[9] = new Integer((hop1Distance + hop2Distance)/1609);
                obj[10] = new Integer(rset.getInt("PriceTotal"));

                if(i > (pageNum - 1) * pageBean.rowsPerPage - 1 && i <= pageNum * pageBean.rowsPerPage - 1)
                {
                    if ((hop1Distance < limitDistanceFactor*originalDistance) && (hop2Distance < limitDistanceFactor*originalDistance))
                    {
                        v.add(obj);
                        i++;
                    }
                    else
                    {
                        if (fb.isShowRoundabout())
                        {
                            v.add(obj);
                            i++;
                        }
                    }
                }
                else
                {
                    if ((hop1Distance < limitDistanceFactor*originalDistance) && (hop2Distance < limitDistanceFactor*originalDistance))
                    {
                        i++;
                    }
                    else
                    {
                        if (fb.isShowRoundabout())
                        {
                            i++;
                        }
                    }
                }
                
                distanceRset.close();
            }
            
            // leave to trip: two-stop flight
            rset = stmt.executeQuery(sqlDepart3hop);
            while(rset.next())
            {
                String detailInfo = "";
                String flightnum = "";
                Object[] obj = new Object[11];
                obj[0] = new String("3");
                obj[1] = new String(rset.getString("DEPART_AIRPORT_CITY") + "(" + rset.getString("DEPART_AIRPORT") + ")");
                obj[2] = new String(rset.getString("ARRIVE_AIRPORT_CITY") + "(" + rset.getString("ARRIVE_AIRPORT") + ")");
                obj[3] = new String(rset.getString("FIRST_AIRLINE_NAME") + "/" + rset.getString("SECOND_AIRLINE_NAME") + "/" + rset.getString("THIRD_AIRLINE_NAME"));
                obj[4] = new String(rset.getString("FIRST_FLIGHT_NUMBER") + "/" + rset.getString("SECOND_FLIGHT_NUMBER") + "/" + rset.getString("THIRD_FLIGHT_NUMBER"));
                obj[5] = new String(rset.getString("FIRST_FLIGHT_DEPARTURE_TIME"));
                obj[6] = new String(rset.getString("THIRD_FLIGHT_ARRIVAL_TIME"));
                String departState = rset.getString("DEPART_AIRPORT_STATE");
                String arriveState = rset.getString("ARRIVE_AIRPORT_STATE");
                String mid1State = rset.getString("FIRST_MID_AIRPORT_STATE");
                String mid2State = rset.getString("SECOND_MID_AIRPORT_STATE");
                detailInfo  = "Depart from: " + rset.getString("DEPART_AIRPORT_CITY") + "," + departState + " (" + rset.getString("DEPART_AIRPORT") + ") <br>";
                detailInfo += "Arrive at:   " + rset.getString("ARRIVE_AIRPORT_CITY") + "," + arriveState + " (" + rset.getString("ARRIVE_AIRPORT") + ") <br>";
                detailInfo += "Connects in: " + rset.getString("FIRST_MID_AIRPORT_CITY") + "," + mid1State + " (" + rset.getString("FIRST_MID_AIRPORT") + ") <br>";
                detailInfo += "             " + rset.getString("SECOND_MID_AIRPORT_CITY") + "," + mid2State + " (" + rset.getString("SECOND_MID_AIRPORT") + ") <br>";
                detailInfo += "Operated by: " + rset.getString("FIRST_AIRLINE_NAME") + "/" + rset.getString("SECOND_AIRLINE_NAME")
                                + "/" + rset.getString("THIRD_AIRLINE_NAME") + "<br><br>";
                detailInfo += "Flight 1 from " + rset.getString("DEPART_AIRPORT_CITY") + " to " + rset.getString("FIRST_MID_AIRPORT_CITY") + "<br>";
                detailInfo += "Flight No.:  " + rset.getString("FIRST_AIRLINE_ABBREVIATION") + rset.getString("FIRST_FLIGHT_NUMBER") + "<br>";
                detailInfo += "Departing...." + rset.getString("FIRST_FLIGHT_DEPARTURE_TIME") + "<br>";
                detailInfo += "Arriving....." + rset.getString("FIRST_FLIGHT_ARRIVAL_TIME") + "<br><br>";
                detailInfo += "Flight 2 from " + rset.getString("FIRST_MID_AIRPORT_CITY") + " to " + rset.getString("SECOND_MID_AIRPORT_CITY") + "<br>";
                detailInfo += "Flight No.:  " + rset.getString("SECOND_AIRLINE_ABBREVIATION") + rset.getString("SECOND_FLIGHT_NUMBER") + "<br>";
                detailInfo += "Departing...." + rset.getString("SECOND_FLIGHT_DEPARTURE_TIME") + "<br>";
                detailInfo += "Arriving....." + rset.getString("SECOND_FLIGHT_ARRIVAL_TIME") + "<br><br>";
                detailInfo += "Flight 3 from " + rset.getString("SECOND_MID_AIRPORT_CITY") + " to " + rset.getString("ARRIVE_AIRPORT_CITY") + "<br>";
                detailInfo += "Flight No.:  " + rset.getString("THIRD_AIRLINE_ABBREVIATION") + rset.getString("THIRD_FLIGHT_NUMBER") + "<br>";
                detailInfo += "Departing...." + rset.getString("THIRD_FLIGHT_DEPARTURE_TIME") + "<br>";
                detailInfo += "Arriving....." + rset.getString("THIRD_FLIGHT_ARRIVAL_TIME") + "<br>";
                obj[7] = detailInfo;
                flightnum = rset.getString("FIRST_FLIGHT_ID") + "." + rset.getString("SECOND_FLIGHT_ID") + "." + rset.getString("THIRD_FLIGHT_ID");
                obj[8] = flightnum;

                // calculate distance between each hop, avoiding zigzag flights
                String sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + departState + "' and upper(TO_STATE)='" + arriveState + "'";
                ResultSet distanceRset = distanceStmt.executeQuery(sqlDistances);
                distanceRset.next();
                int originalDistance = distanceRset.getInt("DISTANCE");

                sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + departState + "' and upper(TO_STATE)='" + mid1State + "'";
                distanceRset = distanceStmt.executeQuery(sqlDistances);
                distanceRset.next();
                int hop1Distance = distanceRset.getInt("DISTANCE");

                sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + mid1State + "' and upper(TO_STATE)='" + mid2State + "'";
                distanceRset = distanceStmt.executeQuery(sqlDistances);
                distanceRset.next();
                int hop2Distance = distanceRset.getInt("DISTANCE");

                sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + mid2State + "' and upper(TO_STATE)='" + arriveState + "'";
                distanceRset = distanceStmt.executeQuery(sqlDistances);
                distanceRset.next();
                int hop3Distance = distanceRset.getInt("DISTANCE");
                
                obj[9] = new Integer((hop1Distance + hop2Distance + hop3Distance)/1609);
                obj[10] = new Integer(rset.getInt("PriceTotal"));

                if(i > (pageNum - 1) * pageBean.rowsPerPage - 1 && i <= pageNum * pageBean.rowsPerPage - 1)
                {
                    if ((hop1Distance < limitDistanceFactor*originalDistance) && (hop2Distance < limitDistanceFactor*originalDistance)
                            && (hop3Distance < limitDistanceFactor*originalDistance))
                    {
                        v.add(obj);
                        i++;
                    }
                    else
                    {
                        if (fb.isShowRoundabout())
                        {
                            v.add(obj);
                            i++;
                        }
                    }
                }
                else
                {
                    if ((hop1Distance < limitDistanceFactor*originalDistance) && (hop2Distance < limitDistanceFactor*originalDistance)
                            && (hop3Distance < limitDistanceFactor*originalDistance))
                    {
                        i++;
                    }
                    else
                    {
                        if (fb.isShowRoundabout())
                        {
                            i++;
                        }
                    }
                }
                
                distanceRset.close();
            }
            
            // leave to trip: three-stop flight
            rset = stmt.executeQuery(sqlDepart4hop);
            while(rset.next())
            {
                String detailInfo = "";
                String flightnum = "";
                Object[] obj = new Object[11];
                obj[0] = new String("4");
                obj[1] = new String(rset.getString("DEPART_AIRPORT_CITY") + "(" + rset.getString("DEPART_AIRPORT") + ")");
                obj[2] = new String(rset.getString("ARRIVE_AIRPORT_CITY") + "(" + rset.getString("ARRIVE_AIRPORT") + ")");
                obj[3] = new String(rset.getString("FIRST_AIRLINE_NAME") + "/" + rset.getString("SECOND_AIRLINE_NAME") + "/" + rset.getString("THIRD_AIRLINE_NAME") + "/" + rset.getString("FOURTH_AIRLINE_NAME"));
                obj[4] = new String(rset.getString("FIRST_FLIGHT_NUMBER") + "/" + rset.getString("SECOND_FLIGHT_NUMBER") + "/" + rset.getString("THIRD_FLIGHT_NUMBER") + "/" + rset.getString("FOURTH_FLIGHT_NUMBER"));
                obj[5] = new String(rset.getString("FIRST_FLIGHT_DEPARTURE_TIME"));
                obj[6] = new String(rset.getString("FOURTH_FLIGHT_ARRIVAL_TIME"));
                String departState = rset.getString("DEPART_AIRPORT_STATE");
                String arriveState = rset.getString("ARRIVE_AIRPORT_STATE");
                String mid1State = rset.getString("FIRST_MID_AIRPORT_STATE");
                String mid2State = rset.getString("SECOND_MID_AIRPORT_STATE");
                String mid3State = rset.getString("THIRD_MID_AIRPORT_STATE");
                detailInfo  = "Depart from: " + rset.getString("DEPART_AIRPORT_CITY") + "," + departState + " (" + rset.getString("DEPART_AIRPORT") + ") <br>";
                detailInfo += "Arrive at:   " + rset.getString("ARRIVE_AIRPORT_CITY") + "," + arriveState + " (" + rset.getString("ARRIVE_AIRPORT") + ") <br>";
                detailInfo += "Connects in: " + rset.getString("FIRST_MID_AIRPORT_CITY") + "," + mid1State + " (" + rset.getString("FIRST_MID_AIRPORT") + ") <br>";
                detailInfo += "             " + rset.getString("SECOND_MID_AIRPORT_CITY") + "," + mid2State + " (" + rset.getString("SECOND_MID_AIRPORT") + ") <br>";
                detailInfo += "             " + rset.getString("THIRD_MID_AIRPORT_CITY") + "," + mid3State + " (" + rset.getString("THIRD_MID_AIRPORT") + ") <br>";
                detailInfo += "Operated by: " + rset.getString("FIRST_AIRLINE_NAME") + "/" + rset.getString("SECOND_AIRLINE_NAME")
                                + "/" + rset.getString("THIRD_AIRLINE_NAME") + "/" + rset.getString("FOURTH_AIRLINE_NAME") +"<br><br>";
                detailInfo += "Flight 1 from " + rset.getString("DEPART_AIRPORT_CITY") + " to " + rset.getString("FIRST_MID_AIRPORT_CITY") + "<br>";
                detailInfo += "Flight No.:  " + rset.getString("FIRST_AIRLINE_ABBREVIATION") + rset.getString("FIRST_FLIGHT_NUMBER") + "<br>";
                detailInfo += "Departing...." + rset.getString("FIRST_FLIGHT_DEPARTURE_TIME") + "<br>";
                detailInfo += "Arriving....." + rset.getString("FIRST_FLIGHT_ARRIVAL_TIME") + "<br><br>";
                detailInfo += "Flight 2 from " + rset.getString("FIRST_MID_AIRPORT_CITY") + " to " + rset.getString("SECOND_MID_AIRPORT_CITY") + "<br>";
                detailInfo += "Flight No.:  " + rset.getString("SECOND_AIRLINE_ABBREVIATION") + rset.getString("SECOND_FLIGHT_NUMBER") + "<br>";
                detailInfo += "Departing...." + rset.getString("SECOND_FLIGHT_DEPARTURE_TIME") + "<br>";
                detailInfo += "Arriving....." + rset.getString("SECOND_FLIGHT_ARRIVAL_TIME") + "<br><br>";
                detailInfo += "Flight 3 from " + rset.getString("SECOND_MID_AIRPORT_CITY") + " to " + rset.getString("THIRD_MID_AIRPORT_CITY") + "<br>";
                detailInfo += "Flight No.:  " + rset.getString("THIRD_AIRLINE_ABBREVIATION") + rset.getString("THIRD_FLIGHT_NUMBER") + "<br>";
                detailInfo += "Departing...." + rset.getString("THIRD_FLIGHT_DEPARTURE_TIME") + "<br>";
                detailInfo += "Arriving....." + rset.getString("THIRD_FLIGHT_ARRIVAL_TIME") + "<br><br>";
                detailInfo += "Flight 4 from " + rset.getString("THIRD_MID_AIRPORT_CITY") + " to " + rset.getString("ARRIVE_AIRPORT_CITY") + "<br>";
                detailInfo += "Flight No.:  " + rset.getString("FOURTH_AIRLINE_ABBREVIATION") + rset.getString("FOURTH_FLIGHT_NUMBER") + "<br>";
                detailInfo += "Departing...." + rset.getString("FOURTH_FLIGHT_DEPARTURE_TIME") + "<br>";
                detailInfo += "Arriving....." + rset.getString("FOURTH_FLIGHT_ARRIVAL_TIME") + "<br>";
                obj[7] = detailInfo;
                flightnum = rset.getString("FIRST_FLIGHT_ID") + "." + rset.getString("SECOND_FLIGHT_ID")
                        + "." + rset.getString("THIRD_FLIGHT_ID") + "." + rset.getString("FOURTH_FLIGHT_ID");
                obj[8] = flightnum;

                // calculate distance between each hop, avoiding zigzag flights
                String sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + departState + "' and upper(TO_STATE)='" + arriveState + "'";
                ResultSet distanceRset = distanceStmt.executeQuery(sqlDistances);
                distanceRset.next();
                int originalDistance = distanceRset.getInt("DISTANCE");

                sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + departState + "' and upper(TO_STATE)='" + mid1State + "'";
                distanceRset = distanceStmt.executeQuery(sqlDistances);
                distanceRset.next();
                int hop1Distance = distanceRset.getInt("DISTANCE");

                sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + mid1State + "' and upper(TO_STATE)='" + mid2State + "'";
                distanceRset = distanceStmt.executeQuery(sqlDistances);
                distanceRset.next();
                int hop2Distance = distanceRset.getInt("DISTANCE");

                sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + mid2State + "' and upper(TO_STATE)='" + mid3State + "'";
                distanceRset = distanceStmt.executeQuery(sqlDistances);
                distanceRset.next();
                int hop3Distance = distanceRset.getInt("DISTANCE");

                sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + mid3State + "' and upper(TO_STATE)='" + arriveState + "'";
                distanceRset = distanceStmt.executeQuery(sqlDistances);
                distanceRset.next();
                int hop4Distance = distanceRset.getInt("DISTANCE");
                
                obj[9] = new Integer((hop1Distance + hop2Distance + hop3Distance + hop4Distance)/1609);
                obj[10] = new Integer(rset.getInt("PriceTotal"));

                if(i > (pageNum - 1) * pageBean.rowsPerPage - 1 && i <= pageNum * pageBean.rowsPerPage - 1)
                {
                    if ((hop1Distance < limitDistanceFactor*originalDistance) && (hop2Distance < limitDistanceFactor*originalDistance) 
                            && (hop3Distance < limitDistanceFactor*originalDistance) && (hop4Distance < limitDistanceFactor*originalDistance))
                    {
                        v.add(obj);
                        i++;
                    }
                    else
                    {
                        if (fb.isShowRoundabout())
                        {
                            v.add(obj);
                            i++;
                        }
                    }
                }
                else
                {
                    if ((hop1Distance < limitDistanceFactor*originalDistance) && (hop2Distance < limitDistanceFactor*originalDistance)
                            && (hop3Distance < limitDistanceFactor*originalDistance) && (hop4Distance < limitDistanceFactor*originalDistance))
                    {
                        i++;
                    }
                    else
                    {
                        if (fb.isShowRoundabout())
                        {
                            i++;
                        }
                    }
                }
                
                distanceRset.close();
            }
            
            // leave to trip: four-stop flight
            rset = stmt.executeQuery(sqlDepart5hop);
            while(rset.next())
            {
                String detailInfo = "";
                String flightnum = "";
                Object[] obj = new Object[11];
                obj[0] = new String("5");
                obj[1] = new String(rset.getString("DEPART_AIRPORT_CITY") + "(" + rset.getString("DEPART_AIRPORT") + ")");
                obj[2] = new String(rset.getString("ARRIVE_AIRPORT_CITY") + "(" + rset.getString("ARRIVE_AIRPORT") + ")");
                obj[3] = new String(rset.getString("FIRST_AIRLINE_NAME") + "/" + rset.getString("SECOND_AIRLINE_NAME") + "/" + rset.getString("THIRD_AIRLINE_NAME") + "/" + rset.getString("FOURTH_AIRLINE_NAME") + "/" + rset.getString("FIFTH_AIRLINE_NAME"));
                obj[4] = new String(rset.getString("FIRST_FLIGHT_NUMBER") + "/" + rset.getString("SECOND_FLIGHT_NUMBER") + "/" + rset.getString("THIRD_FLIGHT_NUMBER") + "/" + rset.getString("FOURTH_FLIGHT_NUMBER") + "/" + rset.getString("FIFTH_FLIGHT_NUMBER"));
                obj[5] = new String(rset.getString("FIRST_FLIGHT_DEPARTURE_TIME"));
                obj[6] = new String(rset.getString("FIFTH_FLIGHT_ARRIVAL_TIME"));
                String departState = rset.getString("DEPART_AIRPORT_STATE");
                String arriveState = rset.getString("ARRIVE_AIRPORT_STATE");
                String mid1State = rset.getString("FIRST_MID_AIRPORT_STATE");
                String mid2State = rset.getString("SECOND_MID_AIRPORT_STATE");
                String mid3State = rset.getString("THIRD_MID_AIRPORT_STATE");
                String mid4State = rset.getString("FOURTH_MID_AIRPORT_STATE");
                detailInfo  = "Depart from: " + rset.getString("DEPART_AIRPORT_CITY") + "," + departState + " (" + rset.getString("DEPART_AIRPORT") + ") <br>";
                detailInfo += "Arrive at:   " + rset.getString("ARRIVE_AIRPORT_CITY") + "," + arriveState + " (" + rset.getString("ARRIVE_AIRPORT") + ") <br>";
                detailInfo += "Connects in: " + rset.getString("FIRST_MID_AIRPORT_CITY") + "," + mid1State + " (" + rset.getString("FIRST_MID_AIRPORT") + ") <br>";
                detailInfo += "             " + rset.getString("SECOND_MID_AIRPORT_CITY") + "," + mid2State + " (" + rset.getString("SECOND_MID_AIRPORT") + ") <br>";
                detailInfo += "             " + rset.getString("THIRD_MID_AIRPORT_CITY") + "," + mid3State + " (" + rset.getString("THIRD_MID_AIRPORT") + ") <br>";
                 detailInfo += "             " + rset.getString("FOURTH_MID_AIRPORT_CITY") + "," + mid4State + " (" + rset.getString("FOURTH_MID_AIRPORT") + ") <br>";
                detailInfo += "Operated by: " + rset.getString("FIRST_AIRLINE_NAME") + "/" + rset.getString("SECOND_AIRLINE_NAME")
                                + "/" + rset.getString("THIRD_AIRLINE_NAME") + "/" + rset.getString("FOURTH_AIRLINE_NAME") + "/" + rset.getString("FIFTH_AIRLINE_NAME") +"<br><br>";
                detailInfo += "Flight 1 from " + rset.getString("DEPART_AIRPORT_CITY") + " to " + rset.getString("FIRST_MID_AIRPORT_CITY") + "<br>";
                detailInfo += "Flight No.:  " + rset.getString("FIRST_AIRLINE_ABBREVIATION") + rset.getString("FIRST_FLIGHT_NUMBER") + "<br>";
                detailInfo += "Departing...." + rset.getString("FIRST_FLIGHT_DEPARTURE_TIME") + "<br>";
                detailInfo += "Arriving....." + rset.getString("FIRST_FLIGHT_ARRIVAL_TIME") + "<br><br>";
                detailInfo += "Flight 2 from " + rset.getString("FIRST_MID_AIRPORT_CITY") + " to " + rset.getString("SECOND_MID_AIRPORT_CITY") + "<br>";
                detailInfo += "Flight No.:  " + rset.getString("SECOND_AIRLINE_ABBREVIATION") + rset.getString("SECOND_FLIGHT_NUMBER") + "<br>";
                detailInfo += "Departing...." + rset.getString("SECOND_FLIGHT_DEPARTURE_TIME") + "<br>";
                detailInfo += "Arriving....." + rset.getString("SECOND_FLIGHT_ARRIVAL_TIME") + "<br><br>";
                detailInfo += "Flight 3 from " + rset.getString("SECOND_MID_AIRPORT_CITY") + " to " + rset.getString("THIRD_MID_AIRPORT_CITY") + "<br>";
                detailInfo += "Flight No.:  " + rset.getString("THIRD_AIRLINE_ABBREVIATION") + rset.getString("THIRD_FLIGHT_NUMBER") + "<br>";
                detailInfo += "Departing...." + rset.getString("THIRD_FLIGHT_DEPARTURE_TIME") + "<br>";
                detailInfo += "Arriving....." + rset.getString("THIRD_FLIGHT_ARRIVAL_TIME") + "<br><br>";
                detailInfo += "Flight 4 from " + rset.getString("THIRD_MID_AIRPORT_CITY") + " to " + rset.getString("FOURTH_MID_AIRPORT_CITY") + "<br>";
                detailInfo += "Flight No.:  " + rset.getString("FOURTH_AIRLINE_ABBREVIATION") + rset.getString("FOURTH_FLIGHT_NUMBER") + "<br>";
                detailInfo += "Departing...." + rset.getString("FOURTH_FLIGHT_DEPARTURE_TIME") + "<br>";
                detailInfo += "Arriving....." + rset.getString("FOURTH_FLIGHT_ARRIVAL_TIME") + "<br><br>";
                detailInfo += "Flight 5 from " + rset.getString("FOURTH_MID_AIRPORT_CITY") + " to " + rset.getString("ARRIVE_AIRPORT_CITY") + "<br>";
                detailInfo += "Flight No.:  " + rset.getString("FIFTH_AIRLINE_ABBREVIATION") + rset.getString("FIFTH_FLIGHT_NUMBER") + "<br>";
                detailInfo += "Departing...." + rset.getString("FIFTH_FLIGHT_DEPARTURE_TIME") + "<br>";
                detailInfo += "Arriving....." + rset.getString("FIFTH_FLIGHT_ARRIVAL_TIME") + "<br>";
                obj[7] = detailInfo;
                flightnum = rset.getString("FIRST_FLIGHT_ID") + "." + rset.getString("SECOND_FLIGHT_ID")
                        + "." + rset.getString("THIRD_FLIGHT_ID") + "." + rset.getString("FOURTH_FLIGHT_ID") + "." + rset.getString("FIFTH_FLIGHT_ID");
                obj[8] = flightnum;

                // calculate distance between each hop, avoiding zigzag flights
                String sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + departState + "' and upper(TO_STATE)='" + arriveState + "'";
                ResultSet distanceRset = distanceStmt.executeQuery(sqlDistances);
                distanceRset.next();
                int originalDistance = distanceRset.getInt("DISTANCE");

                sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + departState + "' and upper(TO_STATE)='" + mid1State + "'";
                distanceRset = distanceStmt.executeQuery(sqlDistances);
                distanceRset.next();
                int hop1Distance = distanceRset.getInt("DISTANCE");

                sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + mid1State + "' and upper(TO_STATE)='" + mid2State + "'";
                distanceRset = distanceStmt.executeQuery(sqlDistances);
                distanceRset.next();
                int hop2Distance = distanceRset.getInt("DISTANCE");

                sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + mid2State + "' and upper(TO_STATE)='" + mid3State + "'";
                distanceRset = distanceStmt.executeQuery(sqlDistances);
                distanceRset.next();
                int hop3Distance = distanceRset.getInt("DISTANCE");

                sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + mid3State + "' and upper(TO_STATE)='" + mid4State + "'";
                distanceRset = distanceStmt.executeQuery(sqlDistances);
                distanceRset.next();
                int hop4Distance = distanceRset.getInt("DISTANCE");
                
                sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + mid4State + "' and upper(TO_STATE)='" + arriveState + "'";
                distanceRset = distanceStmt.executeQuery(sqlDistances);
                distanceRset.next();
                int hop5Distance = distanceRset.getInt("DISTANCE");
                
                obj[9] = new Integer((hop1Distance + hop2Distance + hop3Distance + hop4Distance + hop5Distance)/1609);
                obj[10] = new Integer(rset.getInt("PriceTotal"));

                if(i > (pageNum - 1) * pageBean.rowsPerPage - 1 && i <= pageNum * pageBean.rowsPerPage - 1)
                {
                    if ((hop1Distance < limitDistanceFactor*originalDistance) && (hop2Distance < limitDistanceFactor*originalDistance) 
                            && (hop3Distance < limitDistanceFactor*originalDistance) && (hop4Distance < limitDistanceFactor*originalDistance) 
                                && (hop5Distance < limitDistanceFactor*originalDistance))
                    {
                        v.add(obj);
                        i++;
                    }
                    else
                    {
                        if (fb.isShowRoundabout())
                        {
                            v.add(obj);
                            i++;
                        }
                    }
                }
                else
                {
                    if ((hop1Distance < limitDistanceFactor*originalDistance) && (hop2Distance < limitDistanceFactor*originalDistance) 
                            && (hop3Distance < limitDistanceFactor*originalDistance) && (hop4Distance < limitDistanceFactor*originalDistance) 
                                && (hop5Distance < limitDistanceFactor*originalDistance))
                    {
                        i++;
                    }
                    else
                    {
                        if (fb.isShowRoundabout())
                        {
                            i++;
                        }
                    }
                }
                
                distanceRset.close();
            }

            }

            // if round-trip is requested, search for return flight(s)
            if (tripType == 1) {
                // Add return flight(s) (back to trip) into v2 in PageBean
                // back to trip: non-stop flight
                rset = stmt.executeQuery(sqlReturn1hop);
                j=0;
                while(rset.next())
                {
                    if(j > (pageNum - 1) * pageBean.rowsPerPage - 1 && j <= pageNum * pageBean.rowsPerPage - 1)
                    {
                        String detailInfo = "";
                        Object[] obj = new Object[11];
                        obj[0] = new String("1");
                        obj[1] = new String(rset.getString("DEPART_AIRPORT_CITY") + "(" + rset.getString("DEPART_AIRPORT") + ")");
                        obj[2] = new String(rset.getString("ARRIVE_AIRPORT_CITY") + "(" + rset.getString("ARRIVE_AIRPORT") + ")");
                        obj[3] = new String(rset.getString("AIRLINE_NAME"));
                        obj[4] = new String(rset.getString("FLIGHT_NUMBER"));
                        obj[5] = new String(rset.getString("FLIGHT_DEPARTURE_TIME"));
                        obj[6] = new String(rset.getString("FLIGHT_ARRIVAL_TIME"));
                        String departState = rset.getString("DEPART_AIRPORT_STATE");
                        String arriveState = rset.getString("ARRIVE_AIRPORT_STATE");
                        detailInfo  = "Depart from: " + rset.getString("DEPART_AIRPORT_CITY") + "," + departState + " (" + rset.getString("DEPART_AIRPORT") + ") <br>";
                        detailInfo += "Arrive at:   " + rset.getString("ARRIVE_AIRPORT_CITY") + "," + arriveState + " (" + rset.getString("ARRIVE_AIRPORT") + ") <br>";
                        detailInfo += "Operated by: " + rset.getString("AIRLINE_NAME") + "<br>";
                        detailInfo += "Flight No.:  " + rset.getString("AIRLINE_ABBREVIATION") + rset.getString("FLIGHT_NUMBER") + "<br>";
                        detailInfo += "Departing...." + rset.getString("FLIGHT_DEPARTURE_TIME") + "<br>";
                        detailInfo += "Arriving....." + rset.getString("FLIGHT_ARRIVAL_TIME") + "<br>";
                        obj[7] = detailInfo;
                        obj[8] = new String(rset.getString("FIRST_FLIGHT_ID"));
                        
                        // calculate distance between each hop, avoiding zigzag flights
                        String sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + departState + "' and upper(TO_STATE)='" + arriveState + "'";
                        ResultSet distanceRset = distanceStmt.executeQuery(sqlDistances);
                        distanceRset.next();
                        int originalDistance = distanceRset.getInt("DISTANCE");

                        obj[9] = new Integer(originalDistance/1609);
                        obj[10] = new Integer(rset.getInt("PriceTotal"));

                        v2.add(obj);
                    }
                    j++;
                }

                // do not have to consider multiple-hop trips
                if (!fb.isNonStop())
                {
                
                // back to trip: one-stop flight
                rset = stmt.executeQuery(sqlReturn2hop);
                while(rset.next())
                {
                    String detailInfo = "";
                    String flightnum = "";
                    Object[] obj = new Object[11];
                    obj[0] = new String("2");
                    obj[1] = new String(rset.getString("DEPART_AIRPORT_CITY") + "(" + rset.getString("DEPART_AIRPORT") + ")");
                    obj[2] = new String(rset.getString("ARRIVE_AIRPORT_CITY") + "(" + rset.getString("ARRIVE_AIRPORT") + ")");
                    obj[3] = new String(rset.getString("FIRST_AIRLINE_NAME") + "/" + rset.getString("SECOND_AIRLINE_NAME"));
                    obj[4] = new String(rset.getString("FIRST_FLIGHT_NUMBER") + "/" + rset.getString("SECOND_FLIGHT_NUMBER"));
                    obj[5] = new String(rset.getString("FIRST_FLIGHT_DEPARTURE_TIME"));
                    obj[6] = new String(rset.getString("SECOND_FLIGHT_ARRIVAL_TIME"));
                    String departState = rset.getString("DEPART_AIRPORT_STATE");
                    String arriveState = rset.getString("ARRIVE_AIRPORT_STATE");
                    String midState = rset.getString("MID_AIRPORT_STATE");
                    detailInfo  = "Depart from: " + rset.getString("DEPART_AIRPORT_CITY") + "," + departState + " (" + rset.getString("DEPART_AIRPORT") + ") <br>";
                    detailInfo += "Arrive at:   " + rset.getString("ARRIVE_AIRPORT_CITY") + "," + arriveState + " (" + rset.getString("ARRIVE_AIRPORT") + ") <br>";
                    detailInfo += "Connects in: " + rset.getString("MID_AIRPORT_CITY") + "," + midState + " (" + rset.getString("FIRST_MID_AIRPORT") + ") <br>";
                    detailInfo += "Operated by: " + rset.getString("FIRST_AIRLINE_NAME") + "/" + rset.getString("SECOND_AIRLINE_NAME") + "<br><br>";
                    detailInfo += "Flight 1 from " + rset.getString("DEPART_AIRPORT_CITY") + " to " + rset.getString("MID_AIRPORT_CITY") + "<br>";
                    detailInfo += "Flight No.:  " + rset.getString("FIRST_AIRLINE_ABBREVIATION") + rset.getString("FIRST_FLIGHT_NUMBER") + "<br>";
                    detailInfo += "Departing...." + rset.getString("FIRST_FLIGHT_DEPARTURE_TIME") + "<br>";
                    detailInfo += "Arriving....." + rset.getString("FIRST_FLIGHT_ARRIVAL_TIME") + "<br><br>";
                    detailInfo += "Flight 2 from " + rset.getString("MID_AIRPORT_CITY") + " to " + rset.getString("ARRIVE_AIRPORT_CITY") + "<br>";
                    detailInfo += "Flight No.:  " + rset.getString("SECOND_AIRLINE_ABBREVIATION") + rset.getString("SECOND_FLIGHT_NUMBER") + "<br>";
                    detailInfo += "Departing...." + rset.getString("SECOND_FLIGHT_DEPARTURE_TIME") + "<br>";
                    detailInfo += "Arriving....." + rset.getString("SECOND_FLIGHT_ARRIVAL_TIME") + "<br>";
                    obj[7] = detailInfo;
                    flightnum = rset.getString("FIRST_FLIGHT_ID") + "." + rset.getString("SECOND_FLIGHT_ID");
                    obj[8] = flightnum;

                    // calculate distance between each hop, avoiding zigzag flights
                    String sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + departState + "' and upper(TO_STATE)='" + arriveState + "'";
                    ResultSet distanceRset = distanceStmt.executeQuery(sqlDistances);
                    distanceRset.next();
                    int originalDistance = distanceRset.getInt("DISTANCE");

                    sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + departState + "' and upper(TO_STATE)='" + midState + "'";
                    distanceRset = distanceStmt.executeQuery(sqlDistances);
                    distanceRset.next();
                    int hop1Distance = distanceRset.getInt("DISTANCE");

                    sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + midState + "' and upper(TO_STATE)='" + arriveState + "'";
                    distanceRset = distanceStmt.executeQuery(sqlDistances);
                    distanceRset.next();
                    int hop2Distance = distanceRset.getInt("DISTANCE");
                    
                    obj[9] = new Integer((hop1Distance + hop2Distance)/1609);
                    obj[10] = new Integer(rset.getInt("PriceTotal"));

                    if(j > (pageNum - 1) * pageBean.rowsPerPage - 1 && j <= pageNum * pageBean.rowsPerPage - 1)
                    {
                        if ((hop1Distance < limitDistanceFactor*originalDistance) && (hop2Distance < limitDistanceFactor*originalDistance))
                        {
                            v2.add(obj);
                            j++;
                        }
                        else
                        {
                            if (fb.isShowRoundabout())
                            {
                                v2.add(obj);
                                j++;
                            }
                        }
                    }
                    else
                    {
                        if ((hop1Distance < limitDistanceFactor*originalDistance) && (hop2Distance < limitDistanceFactor*originalDistance))
                        {
                            j++;
                        }
                        else
                        {
                            if (fb.isShowRoundabout())
                            {
                                j++;
                            }
                        }
                    }
                    
                    distanceRset.close();
                }

                // back to trip: two-stop flight
                rset = stmt.executeQuery(sqlReturn3hop);
                while(rset.next())
                {
                    String detailInfo = "";
                    String flightnum = "";
                    Object[] obj = new Object[11];
                    obj[0] = new String("3");
                    obj[1] = new String(rset.getString("DEPART_AIRPORT_CITY") + "(" + rset.getString("DEPART_AIRPORT") + ")");
                    obj[2] = new String(rset.getString("ARRIVE_AIRPORT_CITY") + "(" + rset.getString("ARRIVE_AIRPORT") + ")");
                    obj[3] = new String(rset.getString("FIRST_AIRLINE_NAME") + "/" + rset.getString("SECOND_AIRLINE_NAME") + "/" + rset.getString("THIRD_AIRLINE_NAME"));
                    obj[4] = new String(rset.getString("FIRST_FLIGHT_NUMBER") + "/" + rset.getString("SECOND_FLIGHT_NUMBER") + "/" + rset.getString("THIRD_FLIGHT_NUMBER"));
                    obj[5] = new String(rset.getString("FIRST_FLIGHT_DEPARTURE_TIME"));
                    obj[6] = new String(rset.getString("THIRD_FLIGHT_ARRIVAL_TIME"));
                    String departState = rset.getString("DEPART_AIRPORT_STATE");
                    String arriveState = rset.getString("ARRIVE_AIRPORT_STATE");
                    String mid1State = rset.getString("FIRST_MID_AIRPORT_STATE");
                    String mid2State = rset.getString("SECOND_MID_AIRPORT_STATE");
                    detailInfo  = "Depart from: " + rset.getString("DEPART_AIRPORT_CITY") + "," + departState + " (" + rset.getString("DEPART_AIRPORT") + ") <br>";
                    detailInfo += "Arrive at:   " + rset.getString("ARRIVE_AIRPORT_CITY") + "," + arriveState + " (" + rset.getString("ARRIVE_AIRPORT") + ") <br>";
                    detailInfo += "Connects in: " + rset.getString("FIRST_MID_AIRPORT_CITY") + "," + mid1State + " (" + rset.getString("FIRST_MID_AIRPORT") + ") <br>";
                    detailInfo += "             " + rset.getString("SECOND_MID_AIRPORT_CITY") + "," + mid2State + " (" + rset.getString("SECOND_MID_AIRPORT") + ") <br>";
                    detailInfo += "Operated by: " + rset.getString("FIRST_AIRLINE_NAME") + "/" + rset.getString("SECOND_AIRLINE_NAME")
                                    + "/" + rset.getString("THIRD_AIRLINE_NAME") + "<br><br>";
                    detailInfo += "Flight 1 from " + rset.getString("DEPART_AIRPORT_CITY") + " to " + rset.getString("FIRST_MID_AIRPORT_CITY") + "<br>";
                    detailInfo += "Flight No.:  " + rset.getString("FIRST_AIRLINE_ABBREVIATION") + rset.getString("FIRST_FLIGHT_NUMBER") + "<br>";
                    detailInfo += "Departing...." + rset.getString("FIRST_FLIGHT_DEPARTURE_TIME") + "<br>";
                    detailInfo += "Arriving....." + rset.getString("FIRST_FLIGHT_ARRIVAL_TIME") + "<br><br>";
                    detailInfo += "Flight 2 from " + rset.getString("FIRST_MID_AIRPORT_CITY") + " to " + rset.getString("SECOND_MID_AIRPORT_CITY") + "<br>";
                    detailInfo += "Flight No.:  " + rset.getString("SECOND_AIRLINE_ABBREVIATION") + rset.getString("SECOND_FLIGHT_NUMBER") + "<br>";
                    detailInfo += "Departing...." + rset.getString("SECOND_FLIGHT_DEPARTURE_TIME") + "<br>";
                    detailInfo += "Arriving....." + rset.getString("SECOND_FLIGHT_ARRIVAL_TIME") + "<br><br>";
                    detailInfo += "Flight 3 from " + rset.getString("SECOND_MID_AIRPORT_CITY") + " to " + rset.getString("ARRIVE_AIRPORT_CITY") + "<br>";
                    detailInfo += "Flight No.:  " + rset.getString("THIRD_AIRLINE_ABBREVIATION") + rset.getString("THIRD_FLIGHT_NUMBER") + "<br>";
                    detailInfo += "Departing...." + rset.getString("THIRD_FLIGHT_DEPARTURE_TIME") + "<br>";
                    detailInfo += "Arriving....." + rset.getString("THIRD_FLIGHT_ARRIVAL_TIME") + "<br>";
                    obj[7] = detailInfo;
                    flightnum = rset.getString("FIRST_FLIGHT_ID") + "." + rset.getString("SECOND_FLIGHT_ID") + "." + rset.getString("THIRD_FLIGHT_ID");
                    obj[8] = flightnum;

                     // calculate distance between each hop, avoiding zigzag flights
                    String sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + departState + "' and upper(TO_STATE)='" + arriveState + "'";
                    ResultSet distanceRset = distanceStmt.executeQuery(sqlDistances);
                    distanceRset.next();
                    int originalDistance = distanceRset.getInt("DISTANCE");

                    sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + departState + "' and upper(TO_STATE)='" + mid1State + "'";
                    distanceRset = distanceStmt.executeQuery(sqlDistances);
                    distanceRset.next();
                    int hop1Distance = distanceRset.getInt("DISTANCE");

                    sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + mid1State + "' and upper(TO_STATE)='" + mid2State + "'";
                    distanceRset = distanceStmt.executeQuery(sqlDistances);
                    distanceRset.next();
                    int hop2Distance = distanceRset.getInt("DISTANCE");

                    sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + mid2State + "' and upper(TO_STATE)='" + arriveState + "'";
                    distanceRset = distanceStmt.executeQuery(sqlDistances);
                    distanceRset.next();
                    int hop3Distance = distanceRset.getInt("DISTANCE");
                    
                    obj[9] = new Integer((hop1Distance + hop2Distance + hop3Distance)/1609);
                    obj[10] = new Integer(rset.getInt("PriceTotal"));
                    
                    if(j > (pageNum - 1) * pageBean.rowsPerPage - 1 && j <= pageNum * pageBean.rowsPerPage - 1)
                    {
                        if ((hop1Distance < limitDistanceFactor*originalDistance) && (hop2Distance < limitDistanceFactor*originalDistance)
                                && (hop3Distance < limitDistanceFactor*originalDistance))
                        {
                            v2.add(obj);
                            j++;
                        }
                        else
                        {
                            if (fb.isShowRoundabout())
                            {
                                v2.add(obj);
                                j++;
                            }
                        }
                    }
                    else
                    {
                        if ((hop1Distance < limitDistanceFactor*originalDistance) && (hop2Distance < limitDistanceFactor*originalDistance) 
                                && (hop3Distance < limitDistanceFactor*originalDistance))
                        {
                            j++;
                        }
                        else
                        {
                            if (fb.isShowRoundabout())
                            {
                                j++;
                            }
                        }
                    }
                    
                    distanceRset.close();
                }
                
                // back to trip: three-stop flight
                rset = stmt.executeQuery(sqlReturn4hop);
                while(rset.next())
                {
                    String detailInfo = "";
                    String flightnum = "";
                    Object[] obj = new Object[11];
                    obj[0] = new String("4");
                    obj[1] = new String(rset.getString("DEPART_AIRPORT_CITY") + "(" + rset.getString("DEPART_AIRPORT") + ")");
                    obj[2] = new String(rset.getString("ARRIVE_AIRPORT_CITY") + "(" + rset.getString("ARRIVE_AIRPORT") + ")");
                    obj[3] = new String(rset.getString("FIRST_AIRLINE_NAME") + "/" + rset.getString("SECOND_AIRLINE_NAME") + "/" + rset.getString("THIRD_AIRLINE_NAME") + "/" + rset.getString("FOURTH_AIRLINE_NAME"));
                    obj[4] = new String(rset.getString("FIRST_FLIGHT_NUMBER") + "/" + rset.getString("SECOND_FLIGHT_NUMBER") + "/" + rset.getString("THIRD_FLIGHT_NUMBER") + "/" + rset.getString("FOURTH_FLIGHT_NUMBER"));
                    obj[5] = new String(rset.getString("FIRST_FLIGHT_DEPARTURE_TIME"));
                    obj[6] = new String(rset.getString("FOURTH_FLIGHT_ARRIVAL_TIME"));
                    String departState = rset.getString("DEPART_AIRPORT_STATE");
                    String arriveState = rset.getString("ARRIVE_AIRPORT_STATE");
                    String mid1State = rset.getString("FIRST_MID_AIRPORT_STATE");
                    String mid2State = rset.getString("SECOND_MID_AIRPORT_STATE");
                    String mid3State = rset.getString("THIRD_MID_AIRPORT_STATE");
                    detailInfo  = "Depart from: " + rset.getString("DEPART_AIRPORT_CITY") + "," + departState + " (" + rset.getString("DEPART_AIRPORT") + ") <br>";
                    detailInfo += "Arrive at:   " + rset.getString("ARRIVE_AIRPORT_CITY") + "," + arriveState + " (" + rset.getString("ARRIVE_AIRPORT") + ") <br>";
                    detailInfo += "Connects in: " + rset.getString("FIRST_MID_AIRPORT_CITY") + "," + mid1State + " (" + rset.getString("FIRST_MID_AIRPORT") + ") <br>";
                    detailInfo += "             " + rset.getString("SECOND_MID_AIRPORT_CITY") + "," + mid2State + " (" + rset.getString("SECOND_MID_AIRPORT") + ") <br>";
                    detailInfo += "             " + rset.getString("THIRD_MID_AIRPORT_CITY") + "," + mid3State + " (" + rset.getString("THIRD_MID_AIRPORT") + ") <br>";
                    detailInfo += "Operated by: " + rset.getString("FIRST_AIRLINE_NAME") + "/" + rset.getString("SECOND_AIRLINE_NAME")
                                    + "/" + rset.getString("THIRD_AIRLINE_NAME") + "/" + rset.getString("FOURTH_AIRLINE_NAME") +"<br><br>";
                    detailInfo += "Flight 1 from " + rset.getString("DEPART_AIRPORT_CITY") + " to " + rset.getString("FIRST_MID_AIRPORT_CITY") + "<br>";
                    detailInfo += "Flight No.:  " + rset.getString("FIRST_AIRLINE_ABBREVIATION") + rset.getString("FIRST_FLIGHT_NUMBER") + "<br>";
                    detailInfo += "Departing...." + rset.getString("FIRST_FLIGHT_DEPARTURE_TIME") + "<br>";
                    detailInfo += "Arriving....." + rset.getString("FIRST_FLIGHT_ARRIVAL_TIME") + "<br><br>";
                    detailInfo += "Flight 2 from " + rset.getString("FIRST_MID_AIRPORT_CITY") + " to " + rset.getString("SECOND_MID_AIRPORT_CITY") + "<br>";
                    detailInfo += "Flight No.:  " + rset.getString("SECOND_AIRLINE_ABBREVIATION") + rset.getString("SECOND_FLIGHT_NUMBER") + "<br>";
                    detailInfo += "Departing...." + rset.getString("SECOND_FLIGHT_DEPARTURE_TIME") + "<br>";
                    detailInfo += "Arriving....." + rset.getString("SECOND_FLIGHT_ARRIVAL_TIME") + "<br><br>";
                    detailInfo += "Flight 3 from " + rset.getString("SECOND_MID_AIRPORT_CITY") + " to " + rset.getString("THIRD_MID_AIRPORT_CITY") + "<br>";
                    detailInfo += "Flight No.:  " + rset.getString("THIRD_AIRLINE_ABBREVIATION") + rset.getString("THIRD_FLIGHT_NUMBER") + "<br>";
                    detailInfo += "Departing...." + rset.getString("THIRD_FLIGHT_DEPARTURE_TIME") + "<br>";
                    detailInfo += "Arriving....." + rset.getString("THIRD_FLIGHT_ARRIVAL_TIME") + "<br><br>";
                    detailInfo += "Flight 4 from " + rset.getString("THIRD_MID_AIRPORT_CITY") + " to " + rset.getString("ARRIVE_AIRPORT_CITY") + "<br>";
                    detailInfo += "Flight No.:  " + rset.getString("FOURTH_AIRLINE_ABBREVIATION") + rset.getString("FOURTH_FLIGHT_NUMBER") + "<br>";
                    detailInfo += "Departing...." + rset.getString("FOURTH_FLIGHT_DEPARTURE_TIME") + "<br>";
                    detailInfo += "Arriving....." + rset.getString("FOURTH_FLIGHT_ARRIVAL_TIME") + "<br>";
                    obj[7] = detailInfo;
                    flightnum = rset.getString("FIRST_FLIGHT_ID") + "." + rset.getString("SECOND_FLIGHT_ID")
                            + "." + rset.getString("THIRD_FLIGHT_ID") + "." + rset.getString("FOURTH_FLIGHT_ID");
                    obj[8] = flightnum;

                    // calculate distance between each hop, avoiding zigzag flights
                    String sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + departState + "' and upper(TO_STATE)='" + arriveState + "'";
                    ResultSet distanceRset = distanceStmt.executeQuery(sqlDistances);
                    distanceRset.next();
                    int originalDistance = distanceRset.getInt("DISTANCE");

                    sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + departState + "' and upper(TO_STATE)='" + mid1State + "'";
                    distanceRset = distanceStmt.executeQuery(sqlDistances);
                    distanceRset.next();
                    int hop1Distance = distanceRset.getInt("DISTANCE");

                    sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + mid1State + "' and upper(TO_STATE)='" + mid2State + "'";
                    distanceRset = distanceStmt.executeQuery(sqlDistances);
                    distanceRset.next();
                    int hop2Distance = distanceRset.getInt("DISTANCE");

                    sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + mid2State + "' and upper(TO_STATE)='" + mid3State + "'";
                    distanceRset = distanceStmt.executeQuery(sqlDistances);
                    distanceRset.next();
                    int hop3Distance = distanceRset.getInt("DISTANCE");

                    sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + mid3State + "' and upper(TO_STATE)='" + arriveState + "'";
                    distanceRset = distanceStmt.executeQuery(sqlDistances);
                    distanceRset.next();
                    int hop4Distance = distanceRset.getInt("DISTANCE");
                    
                    obj[9] = new Integer((hop1Distance + hop2Distance + hop3Distance + hop4Distance)/1609);
                    obj[10] = new Integer(rset.getInt("PriceTotal"));
                    
                    if(j > (pageNum - 1) * pageBean.rowsPerPage - 1 && j <= pageNum * pageBean.rowsPerPage - 1)
                    {
                        if ((hop1Distance < limitDistanceFactor*originalDistance) && (hop2Distance < limitDistanceFactor*originalDistance)
                                && (hop3Distance < limitDistanceFactor*originalDistance) && (hop4Distance < limitDistanceFactor*originalDistance))
                        {
                            v2.add(obj);
                            j++;
                        }
                        else
                        {
                            if (fb.isShowRoundabout())
                            {
                                v2.add(obj);
                                j++;
                            }
                        }
                    }
                    else
                    {
                        if ((hop1Distance < limitDistanceFactor*originalDistance) && (hop2Distance < limitDistanceFactor*originalDistance)
                                && (hop3Distance < limitDistanceFactor*originalDistance) && (hop4Distance < limitDistanceFactor*originalDistance))
                        {
                            j++;
                        }
                        else
                        {
                            if (fb.isShowRoundabout())
                            {
                                j++;
                            }
                        }
                    }

                    distanceRset.close();
                }
                
                // back to trip: four-stop flight
                rset = stmt.executeQuery(sqlReturn5hop);
                while(rset.next())
                {
                    String detailInfo = "";
                    String flightnum = "";
                    Object[] obj = new Object[11];
                    obj[0] = new String("5");
                    obj[1] = new String(rset.getString("DEPART_AIRPORT_CITY") + "(" + rset.getString("DEPART_AIRPORT") + ")");
                    obj[2] = new String(rset.getString("ARRIVE_AIRPORT_CITY") + "(" + rset.getString("ARRIVE_AIRPORT") + ")");
                    obj[3] = new String(rset.getString("FIRST_AIRLINE_NAME") + "/" + rset.getString("SECOND_AIRLINE_NAME") + "/" + rset.getString("THIRD_AIRLINE_NAME") + "/" + rset.getString("FOURTH_AIRLINE_NAME") + "/" + rset.getString("FIFTH_AIRLINE_NAME") );
                    obj[4] = new String(rset.getString("FIRST_FLIGHT_NUMBER") + "/" + rset.getString("SECOND_FLIGHT_NUMBER") + "/" + rset.getString("THIRD_FLIGHT_NUMBER") + "/" + rset.getString("FOURTH_FLIGHT_NUMBER") + "/" + rset.getString("FIFTH_FLIGHT_NUMBER"));
                    obj[5] = new String(rset.getString("FIRST_FLIGHT_DEPARTURE_TIME"));
                    obj[6] = new String(rset.getString("FIFTH_FLIGHT_ARRIVAL_TIME"));
                    String departState = rset.getString("DEPART_AIRPORT_STATE");
                    String arriveState = rset.getString("ARRIVE_AIRPORT_STATE");
                    String mid1State = rset.getString("FIRST_MID_AIRPORT_STATE");
                    String mid2State = rset.getString("SECOND_MID_AIRPORT_STATE");
                    String mid3State = rset.getString("THIRD_MID_AIRPORT_STATE");
                    String mid4State = rset.getString("FOURTH_MID_AIRPORT_STATE");
                    detailInfo  = "Depart from: " + rset.getString("DEPART_AIRPORT_CITY") + "," + departState + " (" + rset.getString("DEPART_AIRPORT") + ") <br>";
                    detailInfo += "Arrive at:   " + rset.getString("ARRIVE_AIRPORT_CITY") + "," + arriveState + " (" + rset.getString("ARRIVE_AIRPORT") + ") <br>";
                    detailInfo += "Connects in: " + rset.getString("FIRST_MID_AIRPORT_CITY") + "," + mid1State + " (" + rset.getString("FIRST_MID_AIRPORT") + ") <br>";
                    detailInfo += "             " + rset.getString("SECOND_MID_AIRPORT_CITY") + "," + mid2State + " (" + rset.getString("SECOND_MID_AIRPORT") + ") <br>";
                    detailInfo += "             " + rset.getString("THIRD_MID_AIRPORT_CITY") + "," + mid3State + " (" + rset.getString("THIRD_MID_AIRPORT") + ") <br>";
                    detailInfo += "             " + rset.getString("FOURTH_MID_AIRPORT_CITY") + "," + mid4State + " (" + rset.getString("FOURTH_MID_AIRPORT") + ") <br>";
                    detailInfo += "Operated by: " + rset.getString("FIRST_AIRLINE_NAME") + "/" + rset.getString("SECOND_AIRLINE_NAME")
                                    + "/" + rset.getString("THIRD_AIRLINE_NAME") + "/" + rset.getString("FOURTH_AIRLINE_NAME") + "/" + rset.getString("FIFTH_AIRLINE_NAME") + "<br><br>";
                    detailInfo += "Flight 1 from " + rset.getString("DEPART_AIRPORT_CITY") + " to " + rset.getString("FIRST_MID_AIRPORT_CITY") + "<br>";
                    detailInfo += "Flight No.:  " + rset.getString("FIRST_AIRLINE_ABBREVIATION") + rset.getString("FIRST_FLIGHT_NUMBER") + "<br>";
                    detailInfo += "Departing...." + rset.getString("FIRST_FLIGHT_DEPARTURE_TIME") + "<br>";
                    detailInfo += "Arriving....." + rset.getString("FIRST_FLIGHT_ARRIVAL_TIME") + "<br><br>";
                    detailInfo += "Flight 2 from " + rset.getString("FIRST_MID_AIRPORT_CITY") + " to " + rset.getString("SECOND_MID_AIRPORT_CITY") + "<br>";
                    detailInfo += "Flight No.:  " + rset.getString("SECOND_AIRLINE_ABBREVIATION") + rset.getString("SECOND_FLIGHT_NUMBER") + "<br>";
                    detailInfo += "Departing...." + rset.getString("SECOND_FLIGHT_DEPARTURE_TIME") + "<br>";
                    detailInfo += "Arriving....." + rset.getString("SECOND_FLIGHT_ARRIVAL_TIME") + "<br><br>";
                    detailInfo += "Flight 3 from " + rset.getString("SECOND_MID_AIRPORT_CITY") + " to " + rset.getString("THIRD_MID_AIRPORT_CITY") + "<br>";
                    detailInfo += "Flight No.:  " + rset.getString("THIRD_AIRLINE_ABBREVIATION") + rset.getString("THIRD_FLIGHT_NUMBER") + "<br>";
                    detailInfo += "Departing...." + rset.getString("THIRD_FLIGHT_DEPARTURE_TIME") + "<br>";
                    detailInfo += "Arriving....." + rset.getString("THIRD_FLIGHT_ARRIVAL_TIME") + "<br><br>";
                    detailInfo += "Flight 4 from " + rset.getString("THIRD_MID_AIRPORT_CITY") + " to " + rset.getString("FOURTH_MID_AIRPORT_CITY") + "<br>";
                    detailInfo += "Flight No.:  " + rset.getString("FOURTH_AIRLINE_ABBREVIATION") + rset.getString("FOURTH_FLIGHT_NUMBER") + "<br>";
                    detailInfo += "Departing...." + rset.getString("FOURTH_FLIGHT_DEPARTURE_TIME") + "<br>";
                    detailInfo += "Arriving....." + rset.getString("FOURTH_FLIGHT_ARRIVAL_TIME") + "<br><br>";
                    detailInfo += "Flight 5 from " + rset.getString("FOURTH_MID_AIRPORT_CITY") + " to " + rset.getString("ARRIVE_AIRPORT_CITY") + "<br>";
                    detailInfo += "Flight No.:  " + rset.getString("FOURTH_AIRLINE_ABBREVIATION") + rset.getString("FOURTH_FLIGHT_NUMBER") + "<br>";
                    detailInfo += "Departing...." + rset.getString("FOURTH_FLIGHT_DEPARTURE_TIME") + "<br>";
                    detailInfo += "Arriving....." + rset.getString("FOURTH_FLIGHT_ARRIVAL_TIME") + "<br>";
                    obj[7] = detailInfo;
                    flightnum = rset.getString("FIRST_FLIGHT_ID") + "." + rset.getString("SECOND_FLIGHT_ID")
                            + "." + rset.getString("THIRD_FLIGHT_ID") + "." + rset.getString("FOURTH_FLIGHT_ID") + "." + rset.getString("FIFTH_FLIGHT_ID");
                    obj[8] = flightnum;

                    // calculate distance between each hop, avoiding zigzag flights
                    String sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + departState + "' and upper(TO_STATE)='" + arriveState + "'";
                    ResultSet distanceRset = distanceStmt.executeQuery(sqlDistances);
                    distanceRset.next();
                    int originalDistance = distanceRset.getInt("DISTANCE");

                    sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + departState + "' and upper(TO_STATE)='" + mid1State + "'";
                    distanceRset = distanceStmt.executeQuery(sqlDistances);
                    distanceRset.next();
                    int hop1Distance = distanceRset.getInt("DISTANCE");

                    sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + mid1State + "' and upper(TO_STATE)='" + mid2State + "'";
                    distanceRset = distanceStmt.executeQuery(sqlDistances);
                    distanceRset.next();
                    int hop2Distance = distanceRset.getInt("DISTANCE");

                    sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + mid2State + "' and upper(TO_STATE)='" + mid3State + "'";
                    distanceRset = distanceStmt.executeQuery(sqlDistances);
                    distanceRset.next();
                    int hop3Distance = distanceRset.getInt("DISTANCE");

                    sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + mid3State + "' and upper(TO_STATE)='" + mid4State + "'";
                    distanceRset = distanceStmt.executeQuery(sqlDistances);
                    distanceRset.next();
                    int hop4Distance = distanceRset.getInt("DISTANCE");
                    
                    sqlDistances = "select DISTANCE from BL1.STATEDISTANCE where upper(FROM_STATE)='" + mid4State + "' and upper(TO_STATE)='" + arriveState + "'";
                    distanceRset = distanceStmt.executeQuery(sqlDistances);
                    distanceRset.next();
                    int hop5Distance = distanceRset.getInt("DISTANCE");
                    
                    obj[9] = new Integer((hop1Distance + hop2Distance + hop3Distance + hop4Distance)/1609);
                    obj[10] = new Integer(rset.getInt("PriceTotal"));
                    
                    if(j > (pageNum - 1) * pageBean.rowsPerPage - 1 && j <= pageNum * pageBean.rowsPerPage - 1)
                    {
                        if ((hop1Distance < limitDistanceFactor*originalDistance) && (hop2Distance < limitDistanceFactor*originalDistance)
                                && (hop3Distance < limitDistanceFactor*originalDistance) && (hop4Distance < limitDistanceFactor*originalDistance)
                                    && (hop5Distance < limitDistanceFactor*originalDistance))
                        {
                            v2.add(obj);
                            j++;
                        }
                        else
                        {
                            if (fb.isShowRoundabout())
                            {
                                v2.add(obj);
                                j++;
                            }
                        }
                    }
                    else
                    {
                        if ((hop1Distance < limitDistanceFactor*originalDistance) && (hop2Distance < limitDistanceFactor*originalDistance)
                                && (hop3Distance < limitDistanceFactor*originalDistance) && (hop4Distance < limitDistanceFactor*originalDistance)
                                    && (hop5Distance < limitDistanceFactor*originalDistance))
                        {
                            j++;
                        }
                        else
                        {
                            if (fb.isShowRoundabout())
                            {
                                j++;
                            }
                        }
                    }

                    distanceRset.close();
                }

                }
            }

            // clean up
            rset.close();
            stmt.close();
            distanceStmt.close();
            con.close();

            if (currentType == 1)
                pageBean.setMaxRowCount(i);
            else
                pageBean.setMaxRowCount(j);
            pageBean.curPage = pageNum;
            pageBean.data = v;
            pageBean.data2 = v2;
            pageBean.countMaxPage();
            return pageBean;
        }
        catch(Exception e) {
            e.printStackTrace();
            throw e;
        }
    }
    
    public Vector getResult()throws Exception
    {
        return v;
    }

    public FlightBean getFb() {
        return fb;
    }

    public void setFb(FlightBean fb) {
        this.fb = fb;
    }

    public int getTripType() {
        return tripType;
    }

    public void setTripType(int tripType) {
        this.tripType = tripType;
    }

    public int getCurrentType() {
        return currentType;
    }

    public void setCurrentType(int currentType) {
        this.currentType = currentType;
    }
}
